SQL Server Escape String

Question

I'm using this question as a reference. My issue is that it's encoding my string to hex, but not decoding it once it's written to the database.

HTML textarea

<textarea class="form-control" rows="5" name="nomInfo[]" id="appNom" placeholder="Additional Information"></textarea>

Getting POST value and inserting into the DB

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

$nomInfo = $_POST['nomInfo'][0];

$nomInfoDecode = mssql_escape($nomInfo);

$query = "INSERT INTO dbo.emp_recog (nomInfo) VALUES (";
$query .= "'" . $nomInfoDecode . "');";

So for example, if I types in ggfdgdfg/fdg.fdgdf.gdf "fdskfdskfds;fsd ' sdfds' fds/f% into the textarea and submit the form, this is written to the database 0x67676664676466672f6664672e66646764662e676466205c226664736b6664736b6664733b667364205c272073646664735c27206664732f6625

This seems like overkill when a prepared statement would avoid the need for escape strings. EDIT: but to answer your question, remove the apostrophes on the second line of your $query thing. — ZLK, Oct 26 '16 at 22:58
e.g. `$query .= "'" . $nomInfoDecode . "');";` -> `$query .= $nomInfoDecode . ");";` since the apostrophes make you literally put in '0x67676664676466672f6664672e66646764662e676466205c226664736b6664736b6664733b667364205c272073646664735c27206664732f6625' as opposed to what that hex value represents. — ZLK, Oct 26 '16 at 23:04
That makes sense and looks correct but for some reason it still wrote the hex string to the database after doing exactly what you said. @ZLK — collint25, Oct 27 '16 at 13:21

AbraCadaver · Answer 1 · 2022-03-04T15:46:05.007

1

Scrap all the hex stuff, there is no need. Really the only thing to worry about escaping is a quote '. MySQL uses a slash \ as an escape character. MS SQL uses a quote ' to escape a quote ', so you just double-up the quotes:

return str_replace("'", "''", $data );

However, you really should be using PHP Data Objects that supports MS SQL, then there is PDO::quote.

edited Mar 04 '22 at 15:46

answered Oct 26 '16 at 23:08

AbraCadaver

78,200
7
66
87

hope this works every other solution fails me – Technolo Jesus Mar 04 '22 at 14:59
1

@TechnoloJesus Should be using PDO now days, then just use https://www.php.net/manual/en/pdo.quote.php – AbraCadaver Mar 04 '22 at 15:40

score 0 · Answer 2 · answered Oct 26 '16 at 23:05

0

I'm pretty sure the example is not quoting the value to be inserted:

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

which translated to your query using double quotes would be:

$query = "INSERT INTO dbo.emp_recog (nomInfo) VALUES (";
$query .= $nomInfoDecode . ");";

Can you give that a shot?

answered Oct 26 '16 at 23:05

WEBjuju

5,797
4
27
36

btw, i might opt to take advantage of the double quotes like this: $query = "INSERT INTO dbo.emp_recog (nomInfo) VALUES ($nomInfoDecode)"; – WEBjuju Oct 26 '16 at 23:06
That makes sense and looks correct but for some reason it still wrote the hex string to the database after doing exactly what you said. @WEBjuju – collint25 Oct 27 '16 at 13:20

SQL Server Escape String

2 Answers2