Avoiding SQL Injection with MS SQL Server

Do you have questions regarding other database enginges (not MySQL) -- ask here!

Moderators: egami, macek, gesf

Post Reply
landi
php-forum Active User
php-forum Active User
Posts: 388
Joined: Thu Mar 15, 2012 3:59 pm

Fri Oct 24, 2014 6:32 am

Hi,
Does a similar precaution need to be taken when querying SQL Server with the PHP sqlsrv functions at http://php.net/manual/en/ref.sqlsrv.php?
Yes, sqlsrv_prepare.

Generally, prepare functions allow you to pass in parameter values without building up strings of SQL. It is these strings that can cause problems. For example imagine this (abbreviated) code which is meant to return a row if the user is registered, and no row if the user is not registered:

Code: Select all

$sEmail = $_GET['email']; // Get user input
$sPass = $_GET['pass'];

$sSql = "select user_id from users where email = '$sEmail' and pass = '$sPass' ";
//if row exists then user is registered
For the input 'user1', 'my-password', the code will return a row if user1 is registered and the password is correct. However, a malicious user could enter the values below and the code will also work and return a random user id (maybe the first user id in the database - probably the admin user!):

Code: Select all

// Bad user enters this value email and password address: (Note the carefully placed single quotes)
' or  '1' = '1
// this produces a sql statement that looks like this:
select user_id from users where email = '' or '1' = '1' and pass = '' or '1' = '1'  // returns all rows in table
-A

landi
php-forum Active User
php-forum Active User
Posts: 388
Joined: Thu Mar 15, 2012 3:59 pm

Fri Oct 24, 2014 7:23 am

Hi,
Apparently so. I've not used MSsql on PHP, but the manual here http://php.net/manual/en/ref.sqlsrv.php says that sqlsrv_query() does prepare and execute. It's the separation of parameters from the SQL string that gives Injection protection, so any function that takes an array of parameters is protected as long as you also USE the parameters.

i.e. OBVIOUSLY, if you pass a SQL string made up from user input like I showed you then you won't have protection! It is the passing of an array of parameters and using ? in the sql string that gives the protection, so this is safe:

Code: Select all

$sEmail = $_GET['email']; // Get user input
$sPass = $_GET['pass'];

$sSql = "select user_id from users where email = ? and pass = ? ";
// create array of params
// Execute sql + array of params
-A

landi
php-forum Active User
php-forum Active User
Posts: 388
Joined: Thu Mar 15, 2012 3:59 pm

Sat Oct 25, 2014 12:25 am

Hi,
Yes that's the idea, but I've not used MSsql, so subject to testing! In particular, I see the examples appear to pass the parameters by reference:

Code: Select all

$params = array(&$sEmail, &$sPass);
This might be needed by a RETURN parameter, but I can't see why it's needed by an input only param. However you'll soon find out.

- A

Post Reply