SQL Server values with weird characters returns NULL

Question

I need to write a synchronisation between records in SQL Server and our CMS. Everything works fine, but a few records have weird characters.

When I look them up in SQL Management Studio I can see the value "Anselm Grün". When I execute the query in PHP PDO the value is "NULL"

PHP code to execute the query:

    function getResultFromSqlServer($queryString){
    $db = new PDO('odbc:Driver=ODBC Driver 11 for SQL Server; Server=****,1433; Database=Winbooks; UID=****; PWD=****');
    $query = $db->query($queryString);
    $result = array();
    while ($row = $query->fetch()){
        $result[] = $row;
    }//while
    return $result;
}//getResultFromSqlServer

Query:

SELECT name1 as 'Naam', insleep as 'Slapend',
       c_webshop as 'Webshop', tr_groep as 'Groep',
       tr_subgroe as 'Subgroep' 
FROM art 
WHERE (tr_subgroe LIKE '".$logisticsCode."%' 
       OR tr_groep LIKE '".$logisticsCode."%') 
      AND insleep = 0 
ORDER BY name1

(I know I should use prepared statements, but this is only for testing purposes)

Many thanks in advance!

Add "charset=utf8;" to your PDO() string? See https://stackoverflow.com/questions/25807202/how-to-specify-collation-with-pdo-without-set-names — Nilton, Sep 19 '17 at 12:10
I think you're on a wild goose chase here. A return value of `NULL` usually means that no row matched your conditions. Weird characters can get garbled but usually don't have the effect that a fetch will return `NULL`. Then again, you're using ODBC, so who knows? — KIKO Software, Sep 19 '17 at 12:12
Maybe the question wasn't so clear. I get all the records, but the value that has an "ü" returns a NULL-value, but I get the whole record. — MrSkippy, Sep 19 '17 at 12:27
Reduce your query to a simple test case (e.g. `SELECT "Anselm Grün"` or `SELECT name1 FROM art WHERE name1 LIKE "Anselm%"`) and include sample output (e.g. `print_r($row)`.) — miken32, Sep 19 '17 at 21:26
Also, you need to be starting with prepared statements with parameters or you're wide open to SQL injection attacks. What difference does one line of code "only for testing" make? — miken32, Sep 19 '17 at 21:27

score 0 · Answer 1 · answered Sep 19 '17 at 12:32

0

Try to use unicode search

SELECT name1 as 'Naam', insleep as 'Slapend',
       c_webshop as 'Webshop', tr_groep as 'Groep',
       tr_subgroe as 'Subgroep' 
FROM art 
WHERE (tr_subgroe LIKE N'".$logisticsCode."%' 
       OR tr_groep LIKE N'".$logisticsCode."%') 
      AND insleep = 0 
ORDER BY name1

answered Sep 19 '17 at 12:32

Dmitrij Kultasev

5,447
5
44
88

Doesn't work either. I still get the record, but the column that contains ü is empty – MrSkippy Sep 19 '17 at 12:34

SQL Server values with weird characters returns NULL

1 Answers1