AppSec Blog

Various PHP and MySQL Pitfalls

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 ?


Posted July 14, 2009 at 1:20 PM | Permalink | Reply

Bogdan Calin

Nice list.
BTW, not really a security issue but it's good to know that, by default, MySQL queries are cases insensitive.
The queries bellow are returning the same results:
SELECT * FROM users where name = ''JOHN';
SELECT * FROM users where name = ''john';

Posted August 4, 2009 at 10:41 AM | Permalink | Reply


In reference to Bogdan's assertion of query case-insensitivity''
That's only the case when you've set the column, table or database collation type to case insensitivity.
I tend to set my table's collation to utf8_general_ci.
It might be the case that on your system, the collation defaults to case-insensitivity.

Post a Comment


* Indicates a required field.