26

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

31
  • Do you have ATTR_EMULATE_PREPARES enabled or not? Does it make a difference if you change it? Does it make a difference if you use a named parameter like SELECT :param as col and bind [':param' => 'test'] ? I would not expect a change... Commented Jul 7, 2016 at 21:56
  • @MichaelBerkowski I just tried both of those to no avail - same error. Commented Jul 7, 2016 at 22:01
  • What type is a 'this' filed? Commented Jul 7, 2016 at 22:41
  • @zajonc it's a string literal so others can easily reproduce without need for a dataset. My actual use case involves an NVARCHAR(255), but I can't get it to work for any datatype. Commented Jul 7, 2016 at 22:44
  • I suppose it's entirely DBMS issue, with little relation to PDO. Out of curiosity, can you reproduce with all-static query as well? I mean 'this' = 'this'? Commented Jul 8, 2016 at 15:06

4 Answers 4

21
+300

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
Sign up to request clarification or add additional context in comments.

5 Comments

Wow 64824 is spot on the bug I've got too. I covet thy Google-Fu because I never came close to finding that. I'm afraid that the patch was built for a slightly different bug referenced in 61777, but I'm still eager to try. It might take me a couple days to get the time to spin up a test vm to build custom PDO with that patch, but I'm excited thanks!
I compared that patch to PHP 7.0 related source files and can say you are going to do it right. @JeffPuckettII
Today I installed a fresh ubuntu 16.04 on a vm with php7, and confirmed that this issue doesn't exist. I edited your answer to include this option. Tomorrow I will spin up another vm with php5 and compile with the patch to fully confirm your answer, and if it's successful, then I will happily accept your answer.
I did not add Upgrade your PHP to the list since I guessed you are not thinking about this option. So if you are able to do it, you are totally fine. A PHP version > 5.5.x does not come with this issue. @JeffPuckettII
CONFIRMED. I cloned the php source, checked out release 5.5.9, compiled and installed to verify the bug. Then I cherry-picked that commit, recompiled and installed and totally verified that fixed this bug.
5

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.

Comments

0

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.

Comments

0

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.