I know that filtering bad keyword is not a good approach to preventing SQL injection. However, when I couldn't answer why this is not a good approach, here is my rule:

1) When I see ;, I make it to '' (So that no one can make another line of SQL statement).

2) when I see ', I make it to ''. (I believe This should prevent quote escape).

3) When I see *, I make it to &#42 (So that hacker won't get my table info).

4) When I see --, I make it to -(So that it prevents hacker try to comment out my statement).

^ The reason I come up with these 4 rules is because of a SQLi guide in here

Let say I do the following on my server when parsing a SQL statement: db.exc("INSERT INTO name VALUES ('{}', '{}');".format(firsname, lastname))

And when I look at the rule that I set, I think it is technically secure to prevent SQL injection on this statement. Am I correct? If not can you provide a way to break it? (I know the best practice, but I just can't figure out why it is not.) Can someone help me with an example?

  • 3
    The problem is, those 4 rules don't likely cover even a fraction of possible injection methods (there may be characters or encodings you are not accounting for). Blacklisting is rarely, if ever the correct approach for sanitizing input. Apr 14 '18 at 23:03
  • can u suggest a way to break it, I am trying learn from a working example (for instance, how to use a different encoding method to break my scheme)
    – Alex
    Apr 14 '18 at 23:35
  • 2
    @Alex - years of experience have taught that blacklists frequently break, so they should never be trusted. Most security experts wouldn't say "If you can't break it, then it must be secure." Instead they say "I've seen too many blacklists fail to ever trust one again." Apr 15 '18 at 1:03

One obvious way to breach this: attacks using things that aren't strings (for example, numbers) where the user is supposed to enter something like 45 (which is manipulated in code as a string because it comes out of some HTML that way, but is not apostrophe-delimited in the SQL because it's a numeric-type field) but can instead enter an entire SQL subquery (or a procedure call, or at least some additional clauses...).

You also aren't anywhere close to comprehensive about meta-characters to catch. For example, using -- to start a comment isn't the only way (I'm not sure if it's even in the general SQL standard); another way to start a single-line comment (which MySQL also supports) is #, and you aren't doing anything about those. See https://dev.mysql.com/doc/refman/5.7/en/comments.html

Other problems: Let us hypothesize that you managed to actually do this "completely" somehow. Then, some time in the future, you switch database engines (or just upgrade to a new version) and the new database server supports some new meta-character (as a start to a comment, as an end-of-line, as an end-of-string, as a subquery delimiter, as something else altogether...), and suddenly your "solution" is vulnerable again.

Stop trying to re-invent the wheel. Prepared statements / parameterized queries exist for all database drivers. Some database engines support user-defined stored procedures, which are not only parameterized but also execute faster than normal SQL strings.

If you absolutely must combine user input with raw SQL for some reason (this is rare, and can usually be avoided by being more clever with your code, but that are some places where you can't simply use parameterized queries), use a well-tested library function for it. You're basically trying to re-implement mysql_escape_string, and the first attempt at that went wrong too.

  • Thanks for the information! Is there a chance that you provide me a concrete example to demonstrate the way that u suggest it works?
    – Alex
    Apr 15 '18 at 0:52

Your methods of filtering aren't very useful, you should be using built-in security methods instead of replacing characters yourself.

You can do both I guess, but remember that mysqli_query() automatically doesn't accept multiple SQL statements, so that fixes your first issue.

mysql_escape_string() fixes your second issue, and should also fix a range of other problems with values being interpreted as having a larger importance than they should.

What you currently have in place is known as a blacklist, it's not entirely effective unless used in combination with other SQL Injection mitigation methods. I'd recommend using a whitelist if at all possible. So, for example you would only accept integers as input.

Blacklist is like this:

I accept everything except for...

Whitelist is like this:

I disallow everything except for...

So whitelists, when used correctly, are much more effective than a blacklist. Remember that SQL injections are very, very advanced and have existed for a long time. All you have is a blacklist of 4 rules, if it was that simple to prevent all SQL Injection attacks with a blacklist containing just 4 rules, then SQLi wouldn't be an issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.