How to escape strings in SQL Server using PHP? [duplicate]

function ms_escape_string($data) {
        if ( !isset($data) or empty($data) ) return '';
        if ( is_numeric($data) ) return $data;

        $non_displayables = array(
            '/%0[0-8bcef]/',            // url encoded 00-08, 11, 12, 14, 15
            '/%1[0-9a-f]/',             // url encoded 16-31
            '/[\x00-\x08]/',            // 00-08
            '/\x0b/',                   // 11
            '/\x0c/',                   // 12
            '/[\x0e-\x1f]/'             // 14-31
        );
        foreach ( $non_displayables as $regex )
            $data = preg_replace( $regex, '', $data );
        $data = str_replace("'", "''", $data );
        return $data;
    }

Some of the code here was ripped off from CodeIgniter. Works well and is a clean solution.

You could look into the PDO Library. You can use prepared statements with PDO, which will automatically escape any bad characters in your strings if you do the prepared statements correctly. This is for PHP 5 only I think.

i know, litle bit late, but answer from 'Feb 22 '09 at 12:10' by chaos isn`t fit all queries. E.g: "CREATE LOGIN [0x6f6c6f6c6f] FROM WINDOWS" will give you exception

p.s. look at mssql driver for php, http://msdn.microsoft.com/library/cc296181%28v=sql.90%29.aspx and sqlsrv_prepare function, which can binds params.

p.s.s. which also didn`t helps you with query above ;)

After struggling with this for hours, I've come with a solution that feels almost the rightest.

Chaos' answer of converting values to hexastring doesn't works with every datatype, specifically with datetime columns.

I use PHP's PDO::quote(), but as it comes with PHP, PDO::quote() is not supported for MS SQL Server and returns FALSE. The solution for it to work was download some Microsoft's bundles:

• Microsoft Drivers 3.0 for PHP for SQL Server (SQLSRV30.EXE): Download and follow the instructions to install.
• Microsoft® SQL Server® 2012 Native Client: Search through the extensive page for the Native Client. Even though it's 2012, I'm using it to connect to SQL Server 2008 (installing the 2008 Native Client didn't worked). Download and install.

After that you can connect in PHP with PDO using a DSN like the following example:

sqlsrv:Server=192.168.0.25; Database=My_Database;

Using the UID and PWD parameters in the DSN didn't worked, so username and password are passed as the second and third parameters on the PDO constructor when creating the connection. Now you can use PHP's PDO::quote(). Enjoy.

In order to escape single- and double-quotes, you have to double them up:

$value = 'This is a quote, "I said, 'Hi'"';

$value = str_replace( "'", "''", $value ); 

$value = str_replace( '"', '""', $value );

$query = "INSERT INTO TableName ( TextFieldName ) VALUES ( '$value' ) ";

etc...

and attribution: Escape Character In Microsoft SQL Server 2000

You could roll your own version of mysql_real_escape_string, (and improve upon it) with the following regular expression: [\000\010\011\012\015\032\042\047\134\140]. That takes care of the following characters: null, backspace, horizontal tab, new line, carriage return, substitute, double quote, single quote, backslash, grave accent. Backspace and horizontal tab are not supported by mysql_real_escape_string.