将sql server查询替换为secure PDO查询 - replacing sql server query to secure PDO query

replacing sql server query to secure PDO query

本文关键字：查询 secure PDO 替换 server sql | 更新日期: 2023-09-27

我尝试将我的非安全查询替换为PDO(防止SQL注入)，但我不相信我自己在做什么。我有db连接文件:

<?php
$serverName ="db_name'SQLEXPRESS";
 $usr="sa";
 $pwd="SysAdmin1";
 $db="DB"; 
$connectionInfo = array("UID" => $usr, "PWD" => $pwd, "Database" => $db);
$conn = sqlsrv_connect($serverName, $connectionInfo);
?>

和我的文件查询:

require_once 'db_file.php';
$place=$_POST['place'];
$name=$_POST['name'];
$sql_user = "SELECT * FROM users WHERE name='$name' and place= '$place' ";
$res = sqlsrv_query($conn,$sql_user);
$row = sqlsrv_fetch_array($res);

它工作良好，但不安全。我尝试替换为:

$sql_user = $conn -> prepare ("SELECT * FROM users WHERE name = :name and place = :place");  
$sql_user -> execute (array(':name => '$name' ,  :place => '$place''));
$row = $sql_user -> fetch();

我有错误解析错误:语法错误，意外T_VARIABLE，期待')'。我读了很多关于这方面的文章，但没有人认为我做得很好。因为有时查询中的变量是:名称有时只有?

这个适合我:

# connect
try{
    //$pdo = new PDO("sqlsrv:Server=$hostname;Database=$dbname;", $username, $password);  // works with proper driver for PHP.
    $pdo = new PDO("odbc:Driver={SQL Server};Server=$hostname;Database=$dbname;", $username, $password);  // works with proper driver for ODBC and PHP ODBC.
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    ini_set('mssql.charset', 'UTF-8');  // allow Chinese names.
}catch(PDOException $e){
    die("Error connecting to $hostname SQL: ".$e->getMessage());
}
# read
$sql = "SELECT name FROM employees ORDER BY 1";
$stmt = $pdo->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch()){
    echo $row[1]."<br>";
}
# write
try{
    $sql = "INSERT INTO employees(name)
            VALUES (:name)";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':name', $new_employee);
    $stmt->execute();
}catch(PDOException $e){
    echo "Could not add employee $new_employee!";
}

更多教程