So far, we have seen that SQL injections are very dangerous; they also occur very easily and are very easy to find. We will find them everywhere, even in really famous websites. People try to prevent these vulnerabilities using filters. Filters can make it look like there are no exploits, but if we actually try harder, by using different types of encoding, or a proxy, we will be able to bypass most of these filters. Some programmers use a blacklist so, for example, they prevent the use of union and the insert statement. Again, it's not 100% secure, and it can be bypassed. Using a whitelist has exactly the same issues as a blacklist.

The best way to prevent SQLi is to program our web application so that it does not allow code to be injected into it and then executed. So, the best way to do that is to use parameterized statements, where the data and the code are separated. Let's look at an example, we are keeping the least amount of programming in this example. We don't want it to be a programming example (there are actually mistakes in the programming), but we are trying to look at the concept more than how to program it. Following is the example code:

$textbox1 = admin' union select #
Select @ from accounts where username='admin' union select #'

The vulnerable code used Select * from accounts where username is equal to whatever we put in textbox1, and then we put in textbox1, say admin, and then close the quote. Then we're able to do union select and execute something else; once we're done, we add the comment (#), which basically ignores everything that comes in after it. The code looks like this:

Select * from accounts where username ='admin' union select #'

This is very bad and very difficult to protect against. Using filters will only hide the problem, it will not fix it. The best way to fix the vulnerability is using parameterize statements, as in the following example:

prepare("Select * from accounts where username = ?")
execute(array('textbox1'))

This is the safe way to do it. First, we prepare our statement. Most languages, such as PHP, actually have a function where you can prepare ("Select * from accounts where username = ?") and then we send the values. So, PHP now knows the SQL statement is Select * from accounts where username is equal to something, and then it's going to take the value of textbox1. Even if we come in and use our very sneaky statement, which is '$admin' union select #', and paste it in the execute function, the web application will know that the value for textbox1 is admin union select. It will actually try to use Select * from accounts where the username, and then it actually will add its own quotes and try to find username with the inserted username. So, it will be select * from accounts where username ="'$admin' union select#. Therefore, whatever we put in textbox, it will be sent as a value, and the web application will know that this should be a value not code, and it will never execute it. This will protect us against SQL injections.

We can use the filters as a second line of defense. It's also advised that we use the least privilege possible. So, for each database, use one user with the least amount of privileges required; don't allow users to do anything that they want; unless it's a simple website that only does selection, then only allow the user to select. If they only need to select and insert, then only allow them to select and insert; this is a rule we should keep with everything, even with Linux systems. Make sure the permissions are always as minimal as possible, that each user doesn't have any extra permissions they don't need.