Prepared PDO Statement and MS SQL

Question

I finally got my CentOS box talking to our MS SQL box on the network, but now I'm having an issue running a prepared PDO statement to it. The below statement works if I remove the :desc from the end and put in the variable name or the word itself. Does MS SQL not like these sort of statements or am I just missing something?

$cn = "AARONS";

$result = $pdo->prepare("SELECT * from CommonNameAddress where CommonName = :desc");
$result->execute(array(':desc' => $cn));

edit

After implementing some error checking, the error returned is:

Array (
 [0] => 22001
 [1] => 0
 [2] => [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation (SQLExecute[0] at /builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254)
 [3] => 22001
)

check to see if the version of mysql supports that feature, my guess is that it doesn't. — robbmj, Dec 10 '13 at 21:56
The MS SQL server is a 2005, but that shouldn't matter should it? Isn't the PHP script doing all the work and then shipping off the SQL Select? — Josh Craine, Dec 10 '13 at 22:04
I don't know how PDO works internally, but try renaming the 'desc' placeholder with other variable name and try again... — Leonardo, Dec 10 '13 at 22:25
I'm at a loss with this now. I switched over and installed FreeTDS and it appears the driver is functioning correctly and I'm getting all the right returns from the odbcinst commands to check the drivers. Now however, whenever I try to use new driver with the same code as posted above, I get a white screen when using variables. When I switched the execute->array over to a bindParam/Value it returned a normal page, but with no results. If this keeps up I may just have to do a normal query instead of a prepared. Whats the best way to sanitize the input to just use normal PHP variables? — Josh Craine, Dec 11 '13 at 16:15

score 1 · Answer 1 · edited Aug 07 '17 at 19:47

1

Ended up needing a like instead of = after the WHERE part:

$result = $pdo->prepare("SELECT * from CommonNameAddress where CommonName like :name");

$result->execute(array(':name' => "%$cn%"));

edited Aug 07 '17 at 19:47

Yann Chabot

4,789
3
39
56

answered Dec 12 '13 at 17:15

Josh Craine

33
5

score 0 · Answer 2 · answered Dec 10 '13 at 21:57

0

Maybe check your returns and see if an error occurred?

if( ! $result = $pdo->prepare("SELECT * from CommonNameAddress where CommonName = :desc") ) {
  print_r( $pdo->errorInfo() );
} else if( !$result->execute(array(':desc' => $cn)) ) {
  print_r( $result->errorInfo() );
} else {
  //success
}

answered Dec 10 '13 at 21:57

Sammitch

30,782
7
50
77

Hmm, returned me this: Array ( [0] => 22001 [1] => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation (SQLExecute[0] at /builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254) [3] => 22001 ) – Josh Craine Dec 10 '13 at 22:06
Pretty sure that that means your data is longer than the field size. – Sammitch Dec 10 '13 at 22:11
How could the data be too long? If I replace the PDO variable, it returns just fine. – Josh Craine Dec 10 '13 at 23:06
1

It could also be a bug, try adding `$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, TRUE )` immediately after you create the PDO object. – Sammitch Dec 10 '13 at 23:26
I'm thinking it could be some weird bug or bad implementation on my part for whatever reason. This coding works with the MySQL PDO stuff I have my other pages, just doesn't like the MS SQL Server $pdo = new PDO("odbc:NWS","username","password"); $pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, TRUE ); $cn = "AARONS"; $result = $pdo->prepare("SELECT * from CommonNameAddress where CommonName = :name"); $result->execute(array(':name' => $cn)); – Josh Craine Dec 11 '13 at 00:51
After doing some more Googling, I found a few posts that have this exact same issue. Apparently MS's driver is garbage when you try to pass it PDO variables: http://stackoverflow.com/questions/18441721/microsoft-odbc-driver-11-for-sql-server-on-redhat-linux-with-php-gives-an-er http://stackoverflow.com/questions/14628409/im-getting-string-data-right-truncation-errors-from-php-using-odbc-and-conne How annoying.....I guess I'll download and figure out FreeTDS as the second link suggests. – Josh Craine Dec 11 '13 at 01:06

Prepared PDO Statement and MS SQL

edit

2 Answers2

Linked