Sql-server – How to use ‘N’ in the prepared SELECT statement

character-setodbcPHPsql serversql-server-2008

I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.

I'm trying to run the following query which searches the database for Chinese characters:

SELECT TOP (10) ... WHERE (sItemName LIKE N:term1)

        $text = "%简况%";
            $query = $this->DBH->prepare($sql);
            $query->bindParam(':term1', $text, PDO::PARAM_STR );
        $query->execute();

This query returns results with random characters so it looks like the Character encoding is somehow incorrect.

I was able to return the same results by running this query directly on the DB using Heidi:

SELECT TOP (10) ... WHERE (sItemName LIKE '%简况%')

Changing the query to the following by adding an 'N' before the string returned the correct results:

SELECT TOP (10) ... WHERE (sItemName LIKE N'%简况%')

But when I try to do the same with my prepared statement as shown below I get a syntax error:

SELECT TOP (10) ... WHERE (CI.sItemName LIKE N:term1)

        $text = "%简况%";
            $query = $this->DBH->prepare($sql);
            $query->bindParam(':term1', $text, PDO::PARAM_STR );
        $query->execute();

Here's the error:

Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'N:'.

So my question is how do I use 'N' in my prepared statement without getting an error. I'm not a DBO by trade so apologies if I've left anything out!

This returns the expected result:

$seach_term = (string)'简况';
$text = '%'.$seach_term.'%';
$query = $this->DBH->prepare("SELECT * FROM Articles WHERE sHeadline LIKE :term");
$query->bindParam(':term', $text, PDO::PARAM_STR);

But using the PARAM_STR_NATL constant doesn't return anything:

$query->bindParam(':term', $text, PDO::PARAM_STR_NATL);

Best Answer

You should bind them using the (relatively new) PDO_PARAM_STR_NATL constant. This constant is available in PHP 7.2 and up. It will cause the desired N prefix to be added.

$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);

In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818

The Github commit that is linked there shows how N is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129

Related Question