Can an SQL injection be made with a single word in a SELECT statement?

Question

Suppose you have a query looking like this:

SELECT * FROM messages WHERE sender='clean_username'

where the clean_username is received over get/post and sanitized like this:

$clean_username = preg_replace( '/[^A-Za-z0-9_]+/m' , '', $dirty_username );

The above code removes any whitespace (among other things), which means that the valid_username parameter will always only be one word.

What is the simplest way this can be exploited with an injection?

I'm asking this question to better understand how SQL injection works. In my work I stick to the established good practices of using prepared statements and parameterized queries to prevent injections, but I think it's good for people to also have an understanding of how malicious code can be injected in a simple scenario like this.

Answer 1

You can still exploit this using hex coding: stripping spaces is not enough. I guess this is a somewhat interesting place to start. But consider that preg_match()es are pretty bad for performance on high traffic sites.

Prepared statements and parameterized queries are always the best way to prevent SQL injections.

Example of GET injection using hex coding and no spaces

?id=(1)and(1)=(0)union(select(null),group_concat(column_name),(null)from(information_schema.columns)where(table_name)=(0x7573657273))#

I think you can see the problem above.

Answer 2

I think you already answered the question on your own.

The best way is a standard approach where you use parameterized queries to distinguish between user data and sql command.

In your particular case you assume that a sender username can only consist out of a limited set of ASCII characters. That might work for the moment, and as long as there is no string conversion before, no one can easily close the string apostrophes within the sql statement.

But always consider anticipation of changes. Somebody can rely on your given code in the nearby future and use or modify it and make new assumptions. Your test is actually weak and it can suddenly become dangerous when no one remembers and expects it.