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

Question

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

Answer 1

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();