How to properly select from MSSQL using PHP PDO with query, containing cyrillic symbols?

Question

I'am trying to perform simple SELECT query from PHP PDO to MSSQL database. While query contains cyrillic symbols in WHERE condition, result is empty (if not contains, result return successfully). What can I do to correct query?

putenv('ODBCSYSINI=/etc/');
putenv('ODBCINI=/etc/odbc.ini');

$configODBC = config('database.odbc');

try {

    $this->pdo = new PDO('odbc:'. $configODBC['default']['source'], $configODBC['default']['username'], $configODBC['default']['password']);


} catch (PDOException $e) {}
...

$statement      = $this->pdo->prepare("SELECT * FROM [DB].[dbo].table WHERE policy_num LIKE '%cyrillic_symbols%'");
$result         = $statement->execute();

if ($result) {

    while ($out = $statement->fetch()) {

        var_dump($out[0]);

    }
}

PS. MSSQL version 2012. Data in UTF-8 encoding.

Answer 1

I'll post this as an answer, because it's too long for comment. UTF-8 all the way through, suggested by @RiggsFolly has all the answers. In your case, you need to convert values from CP-1251 from/to UTF-8 . I've made this simple test case, see if this will help you:

T-SQL:

CREATE TABLE CyrillicTable (
    [CyrillicText] varchar(200) COLLATE Cyrillic_General_CI_AS
)
INSERT INTO CyrillicTable
    (CyrillicText)
VALUES
    ('Понедельник'),
    ('Вторник')

PHP (file is UTF-8 encoded, using Notepad++):

<?php
# Connection info
$hostname = 'server\instance,port';
$dbname   = 'database';
$username = 'uid';
$pw       = 'pwd';

# Connection
try {
    $dbh = new PDO("odbc:Driver={SQL Server Native Client 11.0};Server=$hostname;Database=$dbname", $username, $pw);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die( "Error connecting to SQL Server. ".$e->getMessage());
}

# Query
try {
    echo "Query"."<br>";
    $sql = "SELECT * FROM dbo.[CyrillicTable] WHERE CyrillicText LIKE '%".iconv('UTF-8', 'CP1251', 'онед')."%'";
    $stmt = $dbh->query($sql);
    while ($row = $stmt->fetch( PDO::FETCH_ASSOC )) {
        foreach($row as $name => $value) {
            echo $name.": ".iconv('CP1251', 'UTF-8', $value)."<br>";
        }   
    }
    echo "<br>";
} catch( PDOException $e ) {
    die( "Error executing query: ".$e->getMessage());
}
$stmt = null;

# Query
try {
    echo "Prepared query"."<br>";
    $sql = "SELECT * FROM dbo.[CyrillicTable] WHERE CyrillicText LIKE ?";
    $stmt = $dbh->prepare($sql);
    $text = 'орни';
    $text = "%$text%";
    $text = iconv('UTF-8', 'CP1251', $text);
    $stmt->bindParam(1, $text, PDO::PARAM_STR);
    $stmt->execute();
    while ($row = $stmt->fetch( PDO::FETCH_ASSOC )) {
        foreach($row as $name => $value) {
            echo $name.": ".iconv('CP1251', 'UTF-8', $value)."<br>";
        }   
    }
    echo "<br>";
} catch( PDOException $e ) {
    die( "Error executing stored procedure: ".$e->getMessage());
}
$stmt = null;


# End
$dbh = null;
?>

Notes:

Consider using parameterized query.