CODEWITHSUNDEEP
phpsql-serverencodingpdo
Abduhafiz
Abduhafiz

Reputation: 3404

Encoding PHP PDO MS SQL Server

I am starting learn PDO with MS SQL Server. Connecting with this code:

$userDB = 'userBD';
$passwordDB = 'passwordDB';

try {
  $DBH = new PDO('mssql:dbname=spr_bank;host=hostname', $userDB, $passwordDB);
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

}
catch(PDOException $e) {
    echo "<h1 style='font-weight:bold; color:red;'>Error. Can not connect to Database</h1>";
    file_put_contents('PDOErrors.log', date("Y-m-d H:i:s")." - ".$e->getMessage()."\n", FILE_APPEND);
    exit();
}

and now, I select data from table:

      //simple query and binding with results
      $query = $DBH->prepare("SELECT name FROM spr_sotrudnik WHERE login = :login AND password = :password");

      $login = (isset($_POST['login']) === true) ? $_POST['login'] : '' ; // ? : shorthand for if else
      $password = (isset($_POST['password']) === true) ? md5($_POST['password']) : '' ; // ? : shorthand for if else

      // bind parameters - avoids SQL injection
      $query->bindValue(':login', $login);
      $query->bindValue(':password', $password);

      //try... if not catch exception
      try {
          // run the query
          $query->execute();

          while($rows = $query->fetch()){
            echo $rows["name"];
          }
      }
      catch(PDOException $e){
          echo $e->getMessage(), $e->getFile(), $e->getLine();
      }

When I go to the browser, I get this error:

SQLSTATE[HY000]: General error: 10007 Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. [10007] (severity 5) [(null)]D:\www\sklad_new\inc\auth.php19

When I write this query $query = $DBH->prepare(" SELECT CONVERT(VARCHAR(MAX),name) AS s FROM spr_sotrudnik WHERE login = :login AND password = :password");

I have no error, but I see "?????"

How can I change this?

Upvotes: 2

Views: 2423

Answers (1)

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146410

You're omitting the charset parameter in the DSN string:

$DBH = new PDO('mssql:dbname=spr_bank;host=hostname', $userDB, $passwordDB);

... so you're probably using default encodings along the whole toolchain.

In any case, the PDO_DBLIB driver is clearly tagged as experimental and includes this notice:

On Windows, you should use SqlSrv, an alternative driver for MS SQL is available from Microsoft: » http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx .

If it is not possible to use SqlSrv, you can use the PDO_ODBC driver to connect to Microsoft SQL Server and Sybase databases, as the native Windows DB-LIB is ancient, thread un-safe and no longer supported by Microsoft.

In my experience, this means that you can obtain all sort of unpredicted side effects.

Upvotes: 2

Related Questions