'PDOException' with message 'SQLSTATE[22001]: String data, right truncated: 0

Question

NOTE: I have narrowed this problem down to specifically PDO because I am able to successfully prepare and execute statements using the odbc_* functions.

Why can't I bind this parameter to the PDO prepared statement?

This works:

$mssqldriver = 'ODBC Driver 13 for SQL Server';
$pdoDB = new PDO("odbc:Driver=$mssqldriver;Server=$hostname;Database=$dbname", $username, $password);
$pdoDB->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql = "SELECT 'value' AS col where 'this' = 'this'";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);
print_r($stmt->fetch());

Array ( [col] => value [0] => value )

Does not work:

$sql = "SELECT 'value' AS col where 'this' = ?";
$stmt = $pdoDB->prepare($sql);
$params = ['this'];
$stmt->execute($params);
print_r($stmt->fetch());

Web Server is running PHP 5.5.9 on Linux Ubuntu 14.04 with ODBC Driver 13 for SQL Server and connecting to Microsoft SQL Server 2012 on Windows Server 2012

Here's the full error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22001]:
String data, right truncated: 0
[Microsoft][ODBC Driver 13 for SQL Server]
String data, right truncation
(SQLExecute[0] at /build/buildd/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)' in /var/www/scratch.php:46
Stack trace:
#0 /var/www/scratch.php(46): PDOStatement->execute(Array)
#1 {main} thrown in /var/www/scratch.php on line 46

I have also tried setting:

$pdoDB->setAttribute( PDO::ATTR_EMULATE_PREPARES, true );

And using named parameters:

$sql = "SELECT 'value' AS col where 'this' = :myVal";
$stmt = $pdoDB->prepare($sql);
$params = ['myVal' => 'this'];
$stmt->execute($params);
print_r($stmt->fetch());

Even with an explicit colon:

$params = [':myVal' => 'this'];

I also tried just using bindParam as demonstrated in this answer:

$sql = "SELECT 'value' AS col where 'this' = ?";
$stmt = $pdoDB->prepare($sql);
$param = 'this';
$stmt->bindParam(1, $param);
$stmt->execute();
print_r($stmt->fetch());

As well as with named parameters:

$sql = "SELECT 'value' AS col where 'this' = :myVal";
$stmt = $pdoDB->prepare($sql);
$param = 'this';
$stmt->bindParam(':myVal', $param, PDO::PARAM_STR);
$stmt->execute();
print_r($stmt->fetch());

If I try to explicitly set the length:

$stmt->bindParam(':myVal', $param, PDO::PARAM_STR, 4);

I get a bonus error:

Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[42000]: Syntax error or access violation: 102
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]
Incorrect syntax near 'OUTPUT'.

And yes, all this is a trivialized example without tables so that you can easily reproduce it, but just to be sure, I have actually tried this with a real table.

CREATE TABLE myTable (
    id INT IDENTITY PRIMARY KEY,
    val NVARCHAR(255)
);
INSERT INTO myTable (val) VALUES ('hello world');

Works:

$sql = "SELECT * FROM myTable WHERE val = 'hello world'";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);
print_r($stmt->fetch());

Array ( [id] => 1 [0] => 1 [val] => hello world [1] => hello world )

Does not work:

$sql = "SELECT * FROM myTable WHERE val = ?";
$stmt = $pdoDB->prepare($sql);
$params = ['hello world'];
$stmt->execute($params);
print_r($stmt->fetch());

All paths lead to the same error:

String data, right truncated

Answer 1

I am facing this error time to time too. It has something to do with real data length against defined length - in my opition :). So for example I have data column nvarchar(50) called Name and

select name from tabname

shows that error. But when I cast output to double-sized nvarchar, it works:

select cast(name as nvarchar(100)) as name from tabname

May be I am far away from real reason of problem, but this solution works for me good.

Answer 2

I had the same error Uncaught Uncaught PDOException: SQLSTATE[22001]. It turned out that the length of the database for password was too short(30) for the encrypted password. So, I just increased it and solved the problem.

Answer 3

Unfortunately,

It's a PDO_ODBC 64-bit incompatibility problem (#61777, #64824) and without any doubts you are on a 64-bit build which doesn't allow you to bind parameters.

Fortunately,

It has a patch that was first included in the 5.6 release:

This bug is also referenced in #61777 and is still present in the latest stable release of the 5.5 branch. I see two tickets exist for this problem already, and I'm just submitting these changes via github as a reminder that this is a serious problem for anyone using PDO_ODBC on the x64 builds.

What is wrong with your PHP's shipped PDO_ODBC?

By looking at one of those recommended patches:

diff --git a/ext/pdo_odbc/odbc_stmt.c b/ext/pdo_odbc/odbc_stmt.c
index 8b0ccf3..1d275cd 100644
--- a/ext/pdo_odbc/odbc_stmt.c
+++ b/ext/pdo_odbc/odbc_stmt.c
@@ -551,7 +551,7 @@ static int odbc_stmt_describe(pdo_stmt_t *stmt, int colno TSRMLS_DC)
    struct pdo_column_data *col = &stmt->columns[colno];
    RETCODE rc;
    SWORD   colnamelen;
-   SDWORD  colsize;
+   SQLULEN colsize;
    SQLLEN displaysize;

We see the only thing that's changed is SDWORD (16-bit signed integer) which is substituted with new ODBC type SQLULEN that is 64 bits in a 64-bit ODBC application and 32 bits in a 32-bit ODBC application.

I believe committer wasn't aware of colsize data type only since in the very next line SQLLEN is defined properly.

What should I do now?

1. Upgrade to PHP version >= 5.6
2. Stick with odbc_* functions as a working solution.
3. Compile a PHP v5.5.9 with provided patches.
4. Build your own PDO wrapper as recommended by @GordonM

Answer 4

This is probably not what you want to hear, but this has all the hallmarks of a bug in PHP's ODBC driver for PDO (which is not heavily used as PHP programmers tend to favour open source databases like MySQL/SQLite/Postgres over commercial offerings), or in the underlying SQL server driver (which is poorly supported in Linux, for similar reasons), though if odbc_* works then it's probably not the underlying driver.

If you try to do the exact same tasks, except using "sqlite::memory:" as the DSN, all your examples work. This makes it highly unlikely that you're doing anything wrong (unless MS Server has some really weird non-conforming SQL syntax of which I'm not aware). Your examples work fine for me with SQLite when ATTR_EMULATE_PREPARES is both enabled and disabled.

All I think you can realistically do is file a bug report and hope somebody picks it up. You may be in for a long wait though.

As for practical solutions to your problem, your options are either a) switch to a DBMS that PHP supports or b) resort to SQL string construction instead of prepared statements and be ready to accept the burden of avoiding SQL injection attacks yourself. This should be considered a last resort though! Using PDO::quote() may help but I'd also make sure your data is very thoroughly validated as well. I know it's not an ideal solution, but if you must use MS SQL and can't wait for the PHP team to bugfix it then I don't really see that you have much of a choice.

Or there's option c) which is use odbc_* functions instead, if they work. Of course if you want to use OOP style then you'll have to implement your own class that wraps the procedural odbc functions in OO methods so that could potentially be a lot of work.

EDIT: I found another question on Stack Overflow where the asker seems to have a similar problem. His solution was to ditch the "official" MS driver in favour of FreeTDS. This might be something of a shot in the dark, but it could be worth a try.