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:…

Accepted Answer

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

How to insert binary data into MSSQL using PDO

Solution

Problem