This is a short post, to summarize some of the issues I see with PHP code and the use of MySQL. Not too many people know about these pitfalls and they are given rise to some of the more subtle security issues:
1 - "SQL Overflow"
If a value you insert into a column is too large, it is truncated silently. This can lead to security issues if you don't validate that the submitted string is of the right length.
2 - "Trailing White Space Ambiguity"
Trailing white spaces are removed by MySQL silently. For example, these two queries retrieve the same result:
select role from user where username='Admin';
select role from user where username='Admin '; (note the space at the end).
3 - Unbalanced Comments
Older versions of MySQL allow /* to be used unbalanced. For example,
select now() /* test
will work. Newer versions of MySQL require balanced comments (unbalanced was always "illegal" according to the documentation
4 - php 'rand()' function returns bad results
If the argument exceeds the maximum integer, you will get not-so random numbers back. This one depends a bit on the version of PHP you are using. But you will not get an error. Instead, you will get negative numbers, or numbers that are not random (e.g. only last couple of digits change).
5 - MySQL "-" comments require a white space
In order to use "-" as a comment, it has to be followed by a whitespace.
select now() -test will fail
select now() — test will work
You don't have to use a space. A tab will work just fine and evades some filters.
Got some to add? Use the comments ?