I'm editing my question to clarify what I'm trying to ask.

In order to launch an SQL injection there should be someway to get input from the user (i.e. username and password) for instance. So I've been exposed to the attack and to the solution itself, the emphasis of the solution was on validating the input and somewhat filtering it from special characters, that was just a simple solution that basically won't be effective because if an authorised user's password contained special characters( i.e #,! etc) then that user won't be able to log into the system. My question is (although that was not the best solution) why would be filter the password's input... won't the username be enough because usually within the SQL query the username's input comes before the password's input so why filter both?

(PS I was also exposed to the parameterized queries which are better than the basic special character filter but I'd like to know what's the use of filtering the password in the first case)

Last edit:

From the answers and comments I got on this question, think I can make it a little more clear...what I was trying to ask is why should we filter the password in addition to filtering the username. If the filtering includes detecting special characters and the user's password contains special characters, then a valid user wont be allowed to enter they system.

closed as unclear what you're asking by Stephane, Xander, Steve, Jens Erat, TildalWave Dec 13 '14 at 1:14

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Validation may have the side effect to also prevent injection in certain cases but it’s not its primary purpose. Generally, DBMS can handle arbitrary binary data, including those characters delimiting string literals. All that is required is passing the data properly to the DMBS. – Gumbo Dec 11 '14 at 7:07
  • 2
    It is not clear what you are asking. Please edit your post to clarify what you mean. – Deer Hunter Dec 11 '14 at 8:10
  • Passwords are a bit of a bad example. You are supposed to hash those (web?) server side (as early as possible) and then store that in the database. – Darsstar Dec 11 '14 at 8:11
  • @DeerHunter I've edited my question and clarified it – Scarl Dec 13 '14 at 16:38
  • @Stephane I've edited my question & received the suitable answer. – Scarl Dec 13 '14 at 16:42

First off, you should not be rolling your own SQL Injection filter. The idea of doing this has been covered at length in the security community and the consensus is that it is almost never a good idea.

My question is all we actually have to filter is the input the user/attacker tries to input in the userID field right?

Not really (but maybe).

Why maybe? Because, ideally passwords are hashed/salted before they are stored in the database. This means that when a user attempts to log in, the password they provide should be hashed/salted before it is compared to what is in the database. If this is done, I do not think that SQL Injection would be possible through the password field because the value entered for the password would look completely different by the time it got to the database.

That was a lot, so let me give a quick example that will hopefully help clarify. In a system that does not hash/salt passwords, the following could be passed in for user/password: abe/' OR '1'='1

And the query that would be executed would be:

SELECT * FROM USERS WHERE name = 'abe' AND password = '' OR '1'='1'

But if the password is salted/hashed, the genereated query for the same values would look like this:

SELECT * FROM USERS WHERE name = 'abe' AND password = '1e54e11980633c7d1fb8a6be99e3e294'

You can see that in the second example the injected SQL becomes hashed and therefore useless for SQLi.

Even though it is theoretically possible that your code would not be vulnerable to SQL Injection, counting on the scenario I described above to protect you would be the wrong way to approach securing your application. When dealing with SQL Injection you should just secure all inputs and not count on anything else protecting you.

In order to properly secure your application from SQL Injection, you will need to use parameterized queries/prepared statements. OWASP has an good introductory article on that here. The exact details on how you would implement this depend on the language you are using.

Honestly, I'm a bit concerned that your professor sent you down the filter track. For someone who is just starting with security, the first thing you should learn is parameterized queries.

  • Parameterizing queries addresses only the single problem of SQL injection. Sanitizing input by restricting it to a whitelist can help address all kinds of generic injection problems, including XPath and SQL injection, directory traversal attacks, and other application flaws. A responsible approach needs to apply both solutions: parameterized SQL plus whitelisted input. It sounds like Scarl's professor is more focused on blacklisting input via filters, which is pretty old-school, and he or she probably could take a more current approach. – John Deters Dec 11 '14 at 16:03
  • This was somewhat what I was looking for! Thank you! – Scarl Dec 13 '14 at 16:33

No, you are missing something. SQL injection can be done on any field that a user can possibly alter. This not only includes your username and your password field, but also any hidden fields that may be stored on the website and passed in to the server (as the user can alter their webpage contents).

If the value is coming from the user's computer in any way, it must be filtered before using it in a SQL statement to prevent SQL injection by filtering. There are a lot of different rules and tricks that can get around simple filters, so a complete safe filter is a complicated thing to make.

The best bet is to use what is known as parametrized SQL which tells SQL that the value of each parameter is a value and not query code. Simply avoid using user input in a SQL query at all if you can.

  • Very brief, it makes a lot of sense right now, so basically filtering whatever comes from the user's side for that matter is the safest approach. – Scarl Dec 13 '14 at 16:34
  • @Scarl the safest is to parameterize your SQL, but if you can't do that for some reason, any input sent from the user's computer that is used in creating a SQL statement must be filtered. – AJ Henderson Dec 13 '14 at 18:50

Not sure if i understood it correctly but here is what i think.

As long as you are using parameterized queries you should be safe in your case. Avoid relying solely on filters as these can be bypassed. But if you are going to use filters, special character you are allowing will cause filtering limitations as well. In such your case You can filter usernames and save password hash in the database and compare hashes from database to avoid injection.This way you can have less limitations when using filtering if implemented correctly. Try not to write use your own blacklist filters for sql injection prevention. There are libraries already for this. You can find them on the link below along with detailed documentation on this topic. But in general try to use parameterized queries wherever possible, avoid constructing dynamic queries with user supplied input and use standard escaping libraries.

For very detailed matter on this subject you can refer to: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

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