CODEWITHSUNDEEP
sqldatabasedatabase-designsql-injection
Susan
Susan

Reputation: 277

SQL Injection after removing all single-quotes and dash-characters

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='[email protected]' 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.)

Upvotes: 15

Views: 12978

Answers (3)

alexglue
alexglue

Reputation: 1322

maybe you should try this string: [email protected]%BF%27 OR true; /* see this link for explanation

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 51000

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'.

Upvotes: 8

krock
krock

Reputation: 29619

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'[email protected] 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, "[email protected]");
ps.setString(2, "foo");
ps.executeQuery();

Upvotes: 16

Related Questions