Illegal mix of collations -- Simple PHP Login

0
votes

Trying to make a simple php login and running into some issues, here's the error:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Here's the query

SELECT * FROM `accounts` WHERE `username` = :user AND `password` = MD5(CONCAT(MD5(`salt`), MD5(:pass)))

Where :user and :pass are preparred statement parameters containing th e username and password.

I'm trying to simulate md5(md5(salt).md5(pass)) in the query.

Using PDO php.

1
phpmysql
You really shouldn't use your own salts on password hashes and you really should use PHP's built-in functions to handle password security. – Jay Blanchard

1 Answers

1
votes

According to the MySql manual on MD5, the collation result of the MD5 function is the same as your system or connection settings.

I would guess that your column collations are set different from your system settings.

You could specify your collation settings when creating your connection. See opening PDO connection.

Without testing, I think something like this would work:

$dsn = 'mysql:host=localhost;dbname=testdb;charset=latin1_swedish_ci';
$username = 'username';
$password = 'password';
$options = array(
   ...
); 

$dbh = new PDO($dsn, $username, $password, $options);