PDO Sqlsrv: auto trimming bindParam on char fields

Question

noticed that if you open a connection with PDO :: SQLSRV_ATTR_ENCODING = PDO :: SQLSRV_ENCODING_UTF8 (default configuration) there is a problem when using LIKE with named parameters on char fields. No automatic trim of the padding spaces is performed, an operation that is performed in all other cases.

How to reproduce the problem

Create a table and insert data in it

CREATE TABLE testDB.dbo.TEST_TABLE (  
    ID_FIELD int IDENTITY(1,1) NOT NULL,  
    CHAR_FIELD char(15) COLLATE Latin1_General_CI_AS DEFAULT ' ' NOT NULL  
    CONSTRAINT TEST_TABLEK00 PRIMARY KEY (ID_FIELD)  
);  
INSERT INTO TEST_TABLE (CHAR_FIELD) VALUES ('Test data'), ('MyString'), ('My data 123');  

Then on PHP I get this results

    $options = array();  
    $pdo = new PDO("sqlsrv:Server=testServer;Database=testDB", 'test', 'test', $options);  
      
    $stmt = $pdo->prepare("SELECT * FROM TEST_TABLE WHERE CHAR_FIELD = 'Test data'");  
    $stmt->execute();  
    $results = $stmt->fetchAll(); //Returns 1 row  
      
    $stmt = $pdo->prepare("SELECT * FROM TEST_TABLE WHERE CHAR_FIELD LIKE 'Test data'");  
    $stmt->execute();  
    $results = $stmt->fetchAll(); //Returns 1 row  
      
    $stmt = $pdo->prepare("SELECT * FROM TEST_TABLE WHERE CHAR_FIELD = :CHAR_FIELD");  
    $value = 'Test data';  
    $stmt->bindParam('CHAR_FIELD', $value, PDO::PARAM_STR);  
    $stmt->execute();  
    $results = $stmt->fetchAll(); //Returns 1 row  
      
    $stmt = $pdo->prepare("SELECT * FROM TEST_TABLE WHERE CHAR_FIELD LIKE :CHAR_FIELD");  
    $value = 'Test data';  
    $stmt->bindParam('CHAR_FIELD', $value, PDO::PARAM_STR);  
    $stmt->execute();  
    $results = $stmt->fetchAll(); //Returns 0 rows  
      
    $stmt = $pdo->prepare("SELECT * FROM TEST_TABLE WHERE CHAR_FIELD LIKE :CHAR_FIELD");  
    $value = 'Test data      ';  
    $stmt->bindParam('CHAR_FIELD', $value, PDO::PARAM_STR);  
    $stmt->execute();  
    $results = $stmt->fetchAll(); //Returns 1 row  
      
    $options = array(PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_SYSTEM);  
    $pdo = new PDO("sqlsrv:Server=testServer;Database=testDB", 'test', 'test', $options);  
    $stmt = $pdo->prepare("SELECT * FROM TEST_TABLE WHERE CHAR_FIELD LIKE :CHAR_FIELD");  
    $value = 'Test data';  
    $stmt->bindParam('CHAR_FIELD', $value, PDO::PARAM_STR);  
    $stmt->execute();  
    $results = $stmt->fetchAll(); //Returns 1 row  

The behavior of the like together with the named parameter, when opening the connection to the DB in UTF8, is not uniform with all the other behaviors. Only in that case is the field not automatically trimmed while in all other cases it is. Personally, it seems to me more of a bug than a deliberate behavior.

Also, I tested this on different enviroments (windows and linux servers, php 5.6 to 7.4) and there are no differences in the behaviour.

I work on a huge application, developed over 10 years and which can interface with different databases (mysql, postgres, oracle and sqlserver). Changing the queries one by one is impossible, I would need a solution at the configuration level (some flags to set in the PDO or on SqlSever itself) or at least find a way to normalize this behavior to all the others automatically via code.

Answer 1

Hi @Francesco Margiotta

This could be happening due to the collation setting you have for the database and the collation setting on the column that you are using in like comparison

CHAR_FIELD char(15) COLLATE Latin1_General_CI_AS DEFAULT ' ' NOT NULL  

Check what happens when you use other collations...

Regards,
Shivam

Answer 2

Hi @Francesco Margiotta

You can try to cast the value as varchar before comparing that is giving the result when I tested but of-course please make sure to test all your scenarios before making these changes in production.

 $stmt = $pdo->prepare("SELECT * FROM TEST_TABLE WHERE CHAR_FIELD LIKE :CHAR_FIELD");  
 $value = 'Test data';  
 $stmt->bindParam('CHAR_FIELD', $value, PDO::PARAM_STR);  
 $stmt->execute();  
 $results = $stmt->fetchAll(); //Returns 0 rows  

SELECT * FROM Test_Table  
WHERE CHAR_FIELD LIKE 'Test data'  --Gives result  
go  
SELECT * FROM Test_Table  
WHERE CHAR_FIELD LIKE N'Test data' --Gives no result assuming this is what is happening when variables are binding with value  
go  
SELECT * FROM Test_Table  
WHERE CHAR_FIELD LIKE CAST(N'Test data' AS varchar); --Gives result with Nprefix   
go  
SELECT * FROM Test_Table  
WHERE CHAR_FIELD LIKE CAST('Test data' AS varchar);  --Gives result without Nprefix too  

Worked with equal sign and change in value (with spaces) which is being compared and like and change in value(with space0 which is being compared too:

I will repeat again please test it thoroughly before using in production not just the resultset wise but also performance wise.

Regards,
Shivam