According to the Open Web Application Security Project (OWASP), SQL injection is the #1 security threat to web applications.

SQL injection attempts occur often. You can recognize them in your logs because they look like this

/employees/?s = name&=%20UNION%20SELECT%20CHAR (45,120,49,45,81,45)

What does it mean? Basically, someone is trying to take over your web application’s database through queries. Thankfully, these attacks are easy to stop if you build some basic defense into your application.

Here is my three-layer defense that I use to stop these types of attacks.

Layer 1: Utilize an ORM

The most important step you can take is to code your database queries and commands so they are not vulnerable to SQL injection. The easiest way to do this is to simply use the Object Relational Mapper (ORM) that is built into popular web frameworks.

Django’s ORM and SQLAlchemy for Flask are great examples. If you use these tools to communicate with the database, and avoid hand-writing SQL queries, your web application will be safe from SQL injection.

Now you must be thinking, perfect! I’m doing that so what else is there to worry about? My response to that is, how are you going to feel when every day you see logs with ‘%select%all’ and ‘%drop%tables’. An attacker is out there just waiting for you to make a mistake. So let’s add another layer of defense.

Layer 2: Sanitize all input

Any input that comes from a user should be treated with extreme suspicion and sanitized.

The easiest way to do this is by using python Django’s clean method for data that is entered through a form:

name = self.cleaned_data['name']

If you are not using Django, or want an extra level of protection, you can write a simple function to sanitize input.

For example, if you have a search form that says ‘Enter a city’, we should think about what normal input looks like. Do city names contain % or * characters? No! Then let’s remove them. We can use this function to remove all special characters in a string:

def sanitize(dirty_string):
    clean_string = re.sub('\W+',' ', dirty_string)
    return clean_string

You should sanitize input that is used for filters or sorting by restricting it to the exact input you require. For example, if you are displaying a list of employees that can be sorted by name, id, or age with a query string like ‘/employees?sort=name’, then return a 404 error if it is not an expected value like so:

sort_params = ['age', 'id', 'name']
sort = request.GET.get('sort')
if sort not in sort_params:
    raise Http404('Invalid sort parameter.)

Layer 3: Use a web application firewall

So we are using an ORM. We are cleaning our queries. Nothing else to worry about, right? Well here is another scenario to consider.

Attackers don’t have time to dilly dally on your web site. They are going to come in, attack like mad looking for vulnerabilities, then leave. You can expect to receive MUCH higher traffic than normal during an attack.

Here’s the other problem. Maybe you are using some kind of caching so that the same query will become cached, reducing load on your web site and database. Well the attacks I have seen send a different query string with every request. So caching is not as effective!

The result is very high load on your database that can easily shut your site down. That’s why you need the third layer of defense, a web application firewall. The one I always recommend to clients is by Cloudflare, and is fairly affordable. It is built to cover the OWASP top threats, and is updated often.

This will detect a suspicious query string and strop it from ever reaching your web site.

In summary, putting these three layers in place will help keep your data safe, and your web application available to your users.