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);

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

  • Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks – Los Porcos Nov 14 at 8:27
  • Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant. – Los Porcos Nov 14 at 9:04
  • Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator. – Los Porcos Nov 15 at 8:01

The N is only needed for literals. In a prepared statement you bind typed parameters to parameter placeholders. The parameters will be typed as NVarchar and set to Unicode values.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.