Avoiding SQL Injection with MS SQL Server

[Anonymous]

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:

$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!):

// 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

 

[Anonymous]

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:

$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

 

[Anonymous]

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:

$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