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!

Not sure about ms sql, but there must be a way to declare a binary column, ie no encoding or collation. — Ja͢ck
– Ja͢ck, Commented Jun 29, 2012 at 13:56
@Jack, Thanks for the comment. After that I tried the binary and varbinary columns, but those do not work either. — Richard
– Richard, Commented Jun 29, 2012 at 14:06
In that case I'm out of ideas besides using base64 encoding :) 25% overhead — Ja͢ck
– Ja͢ck, Commented Jun 29, 2012 at 14:27
Just out of interest, I hope you have a good reason to encrypt a password as opposed to hashing it. Also, you may want to rephrase your question to ask "How to insert binary data into MSSQL using PDO", it may attract more/the right attention. — deceze
– deceze ♦, Commented Jun 29, 2012 at 14:37
Have you tried bindParam(2, $password, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY); — user1440875
– user1440875, Commented Jul 1, 2012 at 0:26

AO_ · Accepted Answer · 2012-07-04 08:20:18Z

2

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

answered Jul 4, 2012 at 8:20

AO_

2,9043 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert binary data into MSSQL using PDO

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related