Is forbiding the ; char enough to prevent SQL injection?

Question

Disclaimer: Parameterized queries are the way to go, no need to discuss about that :)

I've seen the following code at a friends place:

var query = "SELECT * FROM students WHERE name = " + name;

with name being a user provided variable. My obvious reaction was like "Hey John, are you not afraid of SQL injection?". He than stated, that he knows he should parameterize the query but it's not needed for this case as the user is allowed to read everything and he built a simple solution to prevent editing the table/database:

if(query.Contains(';') { throw new Exception(); }

I know I could do something like

name = Felix OR 1 = 1

but this would do no harm as the user is allowed to read everything.

My question is: Is John really right? Is there no way to bypass this simple check to insert, update or delete an entry?

Answer 1

TL;DR No, your friend is not right. INSERT, UPDATE, DELETE and DROP TABLE aren't the be-all and end-all of SQL risks connected to SQL injection.

The very simplest thing I could do with that query is ask repeatedly for a user by the name of SLEEP(3600) (or john' OR SLEEP(3600)=') and, voila, denial of service (as soon as the available connection pool is exhausted - unless memory gets filled up first - while all extant connections are locked waiting for the 3600 second (1 hour) delay to expire).

I could use UNCOMPRESS(HEX2BIN(...)) with a suitable string to trigger a string expansion bomb.

Then I can explore other tables (and possibly even other tablespaces) by using the UNION keyword. If the permissions are set incorrectly and passwords are not properly secured, which happens waaaay more often than you might think, a JOIN against mysql.user can do a world of damage.

And if the user really has "all read privileges", then I could UNION SELECT a nifty variable called @@secure_file_priv and, if it was empty, I could replicate the SLEEP trick using a little-known function called LOAD_FILE(), which under the proper circumstances allows reading - for example - the access passwords to several Web applications that store them, MD5-hashed but not salted (and thus vulnerable to reverse hashing), in insufficiently protected PHP files for ease of recovery.

Finally, what version of MySQL are we talking about? Because this can be deadly important. The ability of using UNION with a known table equals to me being "able to run any SELECT query with the privileges of an authenticated user". In some cases - this is the first I found with a few seconds' googling - this might be a whole lot more than you bargained for.