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!!

Answer 1

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