How to insert binary data into MSSQL using PDO

Question

I am currently experiencing a weird error.

The setup: MSSQL Server 2012 Express with a localdb The target table collation is: SQL_Latin1_General_CP1_CI_AS

PHP 5.3.9 on a Zend Server 5.6 MCrypt with MCRYPT_RIJNDAEL_256 and MCRYPT_MODE_ECB

Sublime Text 2 default encoding (I read it is UTF8 BOM)

I am using PDO with the official MS adapter for the MSSQL server. Everything works fine apart from one thing: I cannot write a row into my administrator table because of the password.

Lets have a look at my ENCRYPTED password:

y"ûƒ^äjw¾bðúl5êù-Ö=W¿Š±¬GP¥Œy÷&ø

This is the PDO Trace:

Array
(
    [0] => IMSSP
    [1] => -7
    [2] => An error occurred translating string for input param 3 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page.


)
SQL: [120] INSERT INTO administrator ( [username], [email], [password], [section] )  VALUES(:username, :email, :password, :section)
Params:  4
Key: Name: [9] :username
paramno=0
name=[9] ":username"
is_param=1
param_type=2
Key: Name: [6] :email
paramno=1
name=[6] ":email"
is_param=1
param_type=2
Key: Name: [9] :password
paramno=2
name=[9] ":password"
is_param=1
param_type=2
Key: Name: [8] :section
paramno=3
name=[8] ":section"
is_param=1
param_type=2

When I use my MSSQL Management Center I can insert my row with the exact same SQL query. The column setup is fine I suppose:

    ["id"]=>
    string(3) "int"
    ["username"]=>
    string(12) "nvarchar(45)"
    ["email"]=>
    string(12) "nvarchar(45)"
    ["password"]=>
    string(12) "varbinary(45)"
    ["section"]=>
    string(11) "nvarchar(7)"
    ["country_code"]=>
    string(11) "nvarchar(2)"

I use prepared statements and the bindParam function with not extra options to execute my SQL statements.

If anybody has an idea, how to solve that please let me know. Anykind of help is appreciated!

Answer 1

You should change your collation to something like utf8_unicode_ci to handle those few unattended characters.

Also you should try something like this:

$db = new PDO('foo', 'bar', 'blo');
$stmt = $db->prepare("select foo, bar from table where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
header("Content-Type: $type");
fpassthru($lob);

To double check the type (this is where the error/bug starts happening) do this check, perhaps even in place of the last line of my example above:

if (is_string($lob)) echo $lob;
else fpassthru($lob);