replacing sql server query to secure PDO query

Question

I try replace my non secure query to PDO (prevent SQL injection) but i not trust my self what are im doing. I have db connection file:

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

and my file with query:

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);

It work fine but not secure. I try replace to :

$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();

I have error Parse error: syntax error, unexpected T_VARIABLE, expecting ')'. I read many post about that but not shore im doing good way or no? Because sometimes variables in query is :name sometimes only ?

Change `(':name => '$name' , :place => '$place'')` to `(:name => $name , :place => $place)` just a few misplaced quotes. I removed them instead. You could have also done `(':name' => '$name' , :place => '$place')` yet the quotes aren't required. — Funk Forty Niner, Aug 10 '14 at 07:30
Thanks @Fred-ii- i have : Fatal error: Call to a member function prepare() on a non-object in — Klapsius, Aug 10 '14 at 07:33
You'll also may need to change `$row = $sql_user -> fetch();` to `while ($row = $sql_user->fetch(PDO::FETCH_ASSOC)){$var = $row['name']; echo $var; }` - I don't know what you're wanting to do with your fetch. — Funk Forty Niner, Aug 10 '14 at 07:39
@Fred-ii- both work fine but i have Fatal error at the moment — Klapsius, Aug 10 '14 at 07:40
And what is diference between WHERE name = :name and WHERE name = ? — Klapsius, Aug 10 '14 at 07:42
$res = sqlsrv_query($conn,$sql_user); $row = sqlsrv_fetch_array($res); — Klapsius, Aug 10 '14 at 07:44
http://php.net/pdo.prepared-statements shows both `:` and `?` placeholders. Maybe some of the examples in there will help you with the problem. — Funk Forty Niner, Aug 10 '14 at 07:48
@Fred-ii- thank you but what are you think about my code above? connection looks good? I change :name => $name , :place => $place but i not understand where fatal error from — Klapsius, Aug 10 '14 at 07:54
You're welcome. Now, was it working before you changed it to a secure method? — Funk Forty Niner, Aug 10 '14 at 07:55
Fatal error: Call to a member function prepare() on a non-object in line 14. Line 14 means $sql_user = $conn -> prepare .... — Klapsius, Aug 10 '14 at 07:59
Instead of `$sql_user = $conn -> prepare` try `$sql_user = $conn -> sqlsrv_prepare` and also `$sql_user -> execute (array` to `$sql_user -> sqlsrv_execute(array` as per documentation http://php.net/manual/en/function.sqlsrv-execute.php — Funk Forty Niner, Aug 10 '14 at 08:00
Fatal error: Call to a member function sqlsrv_prepare() on a non-object in .... — Klapsius, Aug 10 '14 at 08:02
NEVER, EVER post real user name and password in an online forum!! Furthermore, NEVER, EVER use an admnistrator account to connect from your app to the database! Change it to a dedicated login with the minimum permissions required. SQL injection is just one "backdoor" that can be used to hijack your system. If your sa account get in the wrong hands, your whole company is at risk!!! (It's fairly easy to escalate privileges up to a domain admin once someone has enough permissions on a single server - nd based on what I see here, the SQL system accounts most probably aren't secured either). — Lmu92, Aug 10 '14 at 08:28
FYI, you don't need to switch to PDO just to [use prepared statements](http://es1.php.net/sqlsrv_prepare). — Álvaro González, Oct 08 '14 at 16:12

score 0 · Answer 1 · edited May 23 '17 at 12:05

This works for me:

# 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!";
}

replacing sql server query to secure PDO query

1 Answers1