How to Escape Strings in SQL Server Using PHP

How to escape strings in SQL Server using PHP?

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().

How to automatically escape strings in a PHP SQL query?

You should read about prepared statement.

Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):

    `INSERT INTO PRODUCT (name, price) VALUES (?, ?)`

The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.
Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result).

And it's implimentation in PHP: PDO, MySQLi, PostgreSQL and other. So, there is no reason to implement it by yourself. Just use it.

Escape $ character inside a MS SQL query in PHP

You can escape the $ and then it won't be read as a variable by PHP.

echo "\$test";

Demo: https://eval.in/605867 Vs. https://eval.in/605866 (which is empty because $test doesnt exist)

...

or since your query doesn't use single quotes just use single quotes for the string encapsulation.

echo '$test';

mysql_real_escape_string alternative for SQL Server

Nice question, I don't know but you could use PDO::quote() with the PDO_DBLIB driver.

---

EDIT: Seems like this guy got it from StackOverflow:

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

---

Another option:

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

How do you escape quotes in a sql query using php?

addslashes() will escape single quotes with a leading backslash which is valid syntax in MySQL but not in MS SQL Server. The correct way to escape a single quote in MS SQL Server is with another single quote. Use mysql_real_escape_string() for MySQL (mysql_escape_string() has been deprecated). Unfortunately, no analogous mssql_ function exists so you'll have to roll your own using str_replace(), preg_replace() or something similar. Better yet, use a database neutral abstraction layer such as PDO that supports parameterized queries.

---

Related Topics