Why is using DBI's variable-binding causing a MySQL query to fail?

Question

For some reason, using DBI's bind parameter feature for the below AES key is causing a query to fail to find any rows.

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:mysql:database=thedb;host=localhost');

my $aes_key = 'X`ku@wC_BI\SgY[S%/<iaB>&VXd5zDA+';
print length($aes_key), "\n";

my $test = $dbh->selectrow_hashref("SELECT COUNT(*) FROM users WHERE id = ?\
 AND AES_DECRYPT(enc_pass, '$aes_key') IS NOT NULL", undef, 1);
print $test->{'COUNT(*)'}, "\n";

$test = $dbh->selectrow_hashref("SELECT COUNT(*) FROM users WHERE id = ?\
 AND AES_DECRYPT(enc_pass, ?) IS NOT NULL", undef, 1, $aes_key);
print $test->{'COUNT(*)'}, "\n";

Output:

32
1
0

I see there's an escaped "S" in $aes_key, but it doesn't appear to have any impact on the variable since \S isn't a valid escape sequence in Perl. I do suspect that or something similar is the problem, though.

Answer 1

When you bind a variable to a placeholder, MySQL uses exactly what is in the Perl variable. When you interpolate a variable into a SQL statement, MySQL treats it as a string literal.

MySQL handles unknown backslash escapes by removing the backslash. As MySQL string literals, '\S' and 'S' are equivalent. When you use placeholders, '\S' in a Perl variable is equivalent to '\\S' as a MySQL string literal.

It appears that you wound up storing the key incorrectly in the database by using a string literal, so now it can't be found when you use a placeholder. I'll bet that if you change the line where you initialize $aes_key to

my $aes_key = 'X`ku@wC_BISgY[S%/<iaB>&VXd5zDA+'; # note missing backslash

then the results would change to

31
1
1

because that's the key that MySQL has actually been using.

Answer 2

I see there's an escaped "S" in $aes_key, but it doesn't appear to have any impact on the variable since \S isn't a valid escape sequence in Perl. I do suspect that or something similar is the problem, though.

It is and it isn't. With DBI placeholders, there's no interpretation of escape sequences at all.

The problem is that mysql has been interpreting your escape sequences when you pasted the key into SQL:

mysql> select 'X`ku@wC_BI\SgY[S%/<iaB>&VXd5zDA+', length('X`ku@wC_BI\SgY[S%/<iaB>&VXd5zDA+') as len;
+---------------------------------+-----+
| X`ku@wC_BISgY[S%/<iaB>&VXd5zDA+ | len |
+---------------------------------+-----+
| X`ku@wC_BISgY[S%/<iaB>&VXd5zDA+ |  31 |
+---------------------------------+-----+
1 row in set (0.00 sec)

See? No backslash (if you put a backslash before a character that has no special meaning, you just get the same character, but without the backslash). So when you pass the key into mysql correctly it doesn't work, because you were using it incorrectly before.