Sanitizing MSSQL (&/OR Putting HEX into a TEXT Column)

Question

Overview

I'm in need of a way to properly sanitize my MSSQL data. We all know addslashes() and htmlentities() doesn't cut it.

Attempted Solution & Problem

After research, I found this thread here on SO. It worked great, until I needed to insert into a column of type text. When trying to insert a HEX literal into that, I get:

Operand type clash: varbinary is incompatible with text

What I Need

So, I need either another solid sanitizing strategy which doesn't involve HEX literals. OR I need help overcoming this error when inserting HEX into text.

My Current Method:

public static function dbSanitize( $str ){
    if( is_numeric( $str ) )
        return $str;
    $unpacked = unpack( 'H*hex', $str );
    return '0x' . $unpacked['hex'];
}

My Query

[INSERT INTO myTable ( C1,Text2,C3,C4,C5,C6,Text7,C8 ) VALUES ( 111,0x3c703e0a0932323232323c2f703e0a,1,1,1,0,0x5b7b2274797065223a2274657874222c226c6162656c223a224669656c64204e616d65222c2264657363223a22222c224669656c644944223a2239373334313036343937227d5d,1316471975 )].

I'm not beyond changing the type cast of the column, if there's another option for large amounts of text data.

Thanks for any help you can provide!!

score 2 · Accepted Answer · answered Sep 19 '11 at 22:55

2

Don't build your query by appending strings. Use bound fields. See: http://www.php.net/function.mssql-bind.php

Or the $params variable in: http://www.php.net/function.sqlsrv-query.php if you are using the sqlsrv library (which you should).

answered Sep 19 '11 at 22:55

Ariel

25,995
5
59
69

1

Dynamic SQL will always have weaknesses. Bound fields (aka parametrized queries) are not subject to SQL Injection as you let the provider and the database engine handle everything in it's native way of doing things. – mrdenny Sep 19 '11 at 23:02
I tried the method of bound parameters, and it did insert, but it inserted the HEX literals. Reading @mrdenny 's comment, you're suggesting I don't in any way sanitize the input? Are you certain that's safe? Admittedly (and obviously) I don't know much about bound parameters in queries. I just want to make sure this is secure. – ShaneC Sep 19 '11 at 23:12
2

You don't need - and in fact should not! sanitize input in bound variables. The whole point of bound variable is that it handles that for you. – Ariel Sep 19 '11 at 23:56

Sanitizing MSSQL (&/OR Putting HEX into a TEXT Column)

1 Answers1