Query fails randomly whilst inserting with PHP mcrypt

Question

Banging my head against a wall here. But I am using utf8_general_ci encoding and the type is tinyblob in a MySQL DB

I use Mcrypt on rows uname, pwrod and email as seen in the insert. Sometimes this will insert, others it won't. This obviously depends on the string used to generate the encryption.

This is my insert

INSERT INTO `users` ( `uname` , `pword` , `email` , `gender` , `provider` , `level` , `dob` , `confirmed` , `regdate` , `confirmationCode`, `ip` ) 
VALUES ('“­É.¡Ec', '$2a$15$3G.7Pfap0dfWnEZxVPKWjewcLUA6tYm7a1al6I0QNZUCNcdl6E6Mu', 'ðÖŒÅÕ'Ý£mY]ª±¼ ôn´}Ð>d¢', '0','manual', '0', '2014-02-16', '0',NOW(), 'f5ab855e95eab47948b05cfe5a03e4d6', '127.0.0.1' ); 
Error Nr: 1064 Error Msg: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Ý£mY]??? ?n?}?>d?', '0','manual', '0', '2014-02-16', '0',NOW(), 'f5ab855e95eab' at line 14 

If I change the ' to ` then I get the following error:

 Error Nr: 1300 Error Msg: Invalid utf8 character string: '\x93\xAD\xC9.\xA1E\x04c'

I assume I need to change my char encoding? But this is where my knowledge falls down (and just in general all encodings :) )

Answer 1

You are suffering from an SQL injection attack vulnerability:

  [..snip..], 'ðÖŒÅÕ'Ý£mY]ª±¼ ôn´}Ð>d¢',[..snip..]
              ^--start string
                    ^---end string

You are building your query incorrectly - either by NOT escaping the data you're stuff into the query string, or not using your DB library's prepared statements/placeholders functionality.