Escape String in SQL Server using PHP

Question

Whenever I implement this code, I no longer get an error while using a single quote, but the hexstring get's written to the database instead of being converted back to the original characters.

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) . ')
');

This is what I'm trying to do. $suggestTest is the variable I'm using the escape function on.

$nomDept = $_POST['nomDept'];
$subSupervisor = $_POST['subSupervisor'];
$suggestion = $_POST['suggestion'];

$suggestTest = mssql_escape($suggestion);

if ($subSupervisor == "Yes") {
    $query = "INSERT INTO dbo.emp_recog (nomDept, nomSuggestion, subSupervisor) VALUES (";
    $query .= "'" . $nomDept . "', ";
    $query .= "'" . $suggestTest . "', ";
    $query .= "'" . $subSupervisor . "');";
    $res = mssql_query($query);
}

I've also tried omitting the single quotes around the variable like so

if ($subSupervisor == "Yes") {
    $query = "INSERT INTO dbo.emp_recog (nomDept, nomSuggestion, subSupervisor) VALUES (";
    $query .= "'" . $nomDept . "', ";
    $query .= $suggestTest ", ";
    $query .= "'" . $subSupervisor . "');";
    $res = mssql_query($query);
}

can you explain "hexstring get's written to the database instead of being converted back to the original characters" — Mangesh Sathe, Feb 03 '17 at 15:18
@MangeshSatheIND I must have misunderstood the answer to that question. So the intention is for hex to be written to the database using this function? I was under the impression that the function converted the characters to hex to carry out the sql statement, then was converted back to the original characters to write to the DB. — collint25, Feb 03 '17 at 15:27
Users voted negative for unpack('H*hex', $data); just go to http://php.net/manual/en/function.unpack.php#108599 — Mangesh Sathe, Feb 03 '17 at 15:29
hexdec will convert HEX to DEC and then you can pass it to the query — Mangesh Sathe, Feb 03 '17 at 15:30

score -1 · Answer 1 · answered Feb 03 '17 at 15:17

-1

If you use prepare to build your SQL statement, you do not need to escape the variables.

answered Feb 03 '17 at 15:17

David Tilbrook

1

Escape String in SQL Server using PHP

1 Answers1