How to escape strings in SQL Server using PHP?

Question

I'm looking for the alternative of mysql_real_escape_string() for SQL Server. Is addslashes() my best option or there is another alternative function that can be used?

An alternative for mysql_error() would also be useful.

Solution 1

addslashes() isn't fully adequate, but PHP's mssql package doesn't provide any decent alternative. The ugly but fully general solution is encoding the data as a hex bytestring, i.e.

$unpacked = unpack('H*hex', $data);
mssql_query('
    INSERT INTO sometable (somecolumn)
    VALUES (0x' . $unpacked['hex'] . ')
');

Abstracted, that would be:

function mssql_escape($data) {
    if(is_numeric($data))
        return $data;
    $unpacked = unpack('H*hex', $data);
    return '0x' . $unpacked['hex'];
}

mssql_query('
    INSERT INTO sometable (somecolumn)
    VALUES (' . mssql_escape($somevalue) . ')
');

mysql_error() equivalent is mssql_get_last_message().

Solution 2

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.

EDIT: There are plenty of issues with that code snippet above. Please don't use this without reading the comments to know what those are. Better yet, please don't use this at all. Parameterized queries are your friends: http://php.net/manual/en/pdo.prepared-statements.php

Solution 3

Why would you bother escaping anything when you can use parameters in your query?!

sqlsrv_query(
    $connection, 
    'UPDATE some_table SET some_field = ? WHERE other_field = ?', 
    array($_REQUEST['some_field'], $_REQUEST['id'])
)

It works right in selects, deletes, updates regardless whether your values parameters are null or not. Make a matter of principle - Don't concatenate SQL and you are always safe and your queries read much better.

http://php.net/manual/en/function.sqlsrv-query.php

Solution 4

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.

Solution 5

Another way to handle single and double quotes is:

function mssql_escape($str)
{
    if(get_magic_quotes_gpc())
    {
        $str = stripslashes($str);
    }
    return str_replace("'", "''", $str);
}

View more solutions