14

Can anyone show an EXAMPLE of a sql statement when SQL Injection occurred even after all "single-quote" and "dash characters" have been stripped out of the user's input?

SELECT MyRecord   FROM MyTable   
WHERE MyEmail='susan@site.com' AND MyPassword='foo'

(No INTs are involved here.)

Everyone seems to say "yes, I can do it"... but when they are pressed for an e-x-a-m-p-l-e... none of ever shown.

(You can use any version, new or old, of any sql engine: SQL Server, MySql, SqlLite, PostgreSQL, Oracle and countless others.)

Kjartan
  • 18,591
  • 15
  • 71
  • 96
Susan
  • 257
  • 1
  • 5
  • 8
  • 4
    Your SQL will work okay for sites that forbid Irish members (and other people with single quote characters in their names), married women or other people who use hyphenated last names, and people who want to use either character in their passwords. Other than that, you'll have to look for another solution. – Larry Lustig Nov 15 '11 at 17:49
  • Why remove dash? Removing single quotes should be enough. No? – Priyshrm Aug 07 '22 at 07:28

3 Answers3

16

How have you "stripped out of the user's input"? If you have simply removed all occurrences of quotes, then that really isn't Fair for susan.o'reilly@site.com who won't be able to use your website.

If you are escaping each quote with another quote that can cause problems as well. If you passed in \'; DROP TABLE users; -- (at least in MySQL \' is an alternative for escaping quotes) then escaping the single quote would result in an SQL injection attack that would drop the users table:

SELECT MyRecord FROM MyTable
WHERE MyEmail='\''; DROP TABLE MyTable; --' AND MyPassword='foo'

the only real safe method of sanitizing your inputs is By parameterising them:

SELECT MyRecord FROM MyTable
WHERE MyEmail=? AND MyPassword=?

and then add the parameter values using you language of choice, for example in java where ps is a PreparedStatement:

ps.setString(1, "susan@site.com");
ps.setString(2, "foo");
ps.executeQuery();
krock
  • 28,904
  • 13
  • 79
  • 85
8

The problem is not that you can't construct a sanitization system that can avoid SQL injection; you can. The problem is that you can't construct a realistic sanitization system that both guarantees no SQL injection and can be used in any but the most trivial situation.

The example you gave is one of those trivial cases which could never be used in a real product. A real product needs to support both single quotes and hyphens in user names and probably should allow both (at least hyphens) in the password field. Your system does not allow the following:

  • Users with apostrophes in their names, like O'Reilly.

  • Users with hyphenated first (Jean-Pierre) or last (Fortesque-Smythe) names.

  • Users whose email address domains include hyphens.

  • Passwords in which users follow the common recommendation to choose punctuation characters, and want to use hyphen or apostrophe.

So yes, your sanitization is pretty safe against SQL injection. But so would be a system that allowed only upper-case letter 'A'.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
0

maybe you should try this string: valid@email.fu%BF%27 OR true; /* see this link for explanation

Community
  • 1
  • 1
alexglue
  • 1,292
  • 12
  • 18