PHP and SQL Server, how to make it secure?

Question

http://www.php.net/manual/en/book.mssql.php

I am using that to connect from a LAMP environment to SQL Server. I noticed I don't have the neet functions like prepared statement and real_escape_string.

How can I make my query as secure as possible? Any help is appreciated.

Don't suggest me to use ODBC or PDO, I don't have that option. I have to run with what I have, and that's MSSQL.

$con = mssql_connect ('xxx', 'xxx', 'xxx');

mssql_select_db('xxx', $con);

$qry = "SELECT 
            firstname
    FROM 
            person
    where firstname = '{$firstname}'";

$query = mssql_query($qry, $con);

Answer 1

Don't use literals, use parameters and bind them to your query:

$con = mssql_connect ('xxx', 'xxx', 'xxx');
mssql_select_db('xxx', $con);
$qry = 'SELECT firstname
     FROM person
    where firstname = @firstname';
mssql_bind ($qry, '@firstname', $firstname, SQLVARCHAR);
$query = mssql_query($qry, $con);

Answer 2

The MSSQL binding supports prepared statements just fine.

The documentation is your friend.

Answer 3

You can use htmlentities() to convert html elements into html entities and this function accepts a third argument which is for escaping single and double quotes.

Here is the signature of the function:

string htmlentities ( string $string [, int $flags = ENT_COMPAT [, string $charset [, bool $double_encode = true ]]] )

and the arguments that second parameters may take:

ENT_COMPAT Will convert double-quotes and leave single-quotes alone.

ENT_QUOTES Will convert both double and single quotes.

ENT_NOQUOTES Will leave both double and single quotes unconverted.

ENT_IGNORE Silently discard invalid code unit sequences instead of returning an empty string. Added in PHP 5.3.0. This is provided for backwards compatibility; avoid using it as it may have security implications.

And

You can simply use addslashes() with htmlentities()

and also there is another function with cleans html tags out from the fields which is filter_var () and such example look would be:

$return_value = filter_var($data_to_be_filtered,FILTER_SANITIZE_STRING);

Important

Don't forget to check whether magic_quotes are enabled or not. You can do that by writing :

if(get_magic_quotes_gpc())
    //do something

More about magic_quotes: http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-gpc

Edit:

You can do more secure transaction by using prepared-statements. They prevent SQL-Injection.

Sample code:

$db = new mysqli();
$db->real_connect($host,$username,$password,$db) or die("Cannot connect");
$query = "select name from users where id = ?";
$st = $db->prepare($query); //faster than normal query run
$st->bind_param("d",$id);
$st->execute();
$st->bind_result($name);
$st->fetch();
echo $name;

Answer 4

Mssql doesn't supply a function to escape your query. One option is to use "addslashes()" instead, although it is somewhat ugly (and doesn't encompass everything)

This might be helpful: How to escape strings in SQL Server using PHP?