SQL is a programming language that allows programmers to “talk” to large databases using nearly-plain English.
It can access and manipulate data held in a number of tables on a server. When it comes to websites, those tables might include sensitive items like usernames, passwords, credit card numbers, and more.
The problem is, websites can sometimes be easily tricked into running malicious SQL queries when proper precautions aren’t in place.
This kind of attack is called SQL injection, and in this guide, we’ll explain how it works and how to prevent it on your website in 2020.
What Is an SQL Injection Attack and How Do They Work?
Most large websites or web apps will usually have at least one interactive area where the content changes based on a user’s input.
Examples might be:
- Ecommerce sites that populate products based on a search query
- Membership sites that ask for a username and password
- Review sites that allow user-submitted content
When the server receives these inputs (a search for a product, for example), it then translates the entry into a simple SQL query to retrieve and display the right data.
In other cases, it may create a query to check the input against data from a table (like username and password) or add data to a table for storage (like a new user review for a product).
On a website without proper protections in place, hackers can make modifications to their inputs to form malicious queries that create disastrous consequences.
They may add partial elements of SQL queries to the username field, for example, to trick the database into displaying, manipulating, or deleting critical data that only admins should have access to.
(Example: Adding a single quote mark to the input field could end the query the database is trying to create, and allow the hacker to use the UNION function to simply add an entirely new query on to the end of it.)
If their efforts are not impeded, a hacker could, in just a few simple steps:
- Determine if the database has a vulnerability
- Discover the names of different tables of data stored on the database
- Find the names of individual data columns in a given table (username, password, credit card number, and more)
- Delete, manipulate, or display that confidential data
SQL Injection attacks have been prevalent for over a decade. They are well-known in the cyber security industry by now, and most major web companies have gone to extreme lengths to sniff out vulnerabilities.
(WordPress, for example, which powers about 30% of the entire Internet, is constantly patching vulnerabilities with every new version.)
There have been fewer documented cases of successful attacks in recent years, but website owners and database managers need to stay on top of best practices when it comes to web security.
Types of SQL Injections
There are a few different forms this kind of attack can take.
Here are the main ones to be aware of:
In-band SQL Injection
This is the simplest and most common form of SQLi attack, and usually looks a lot like the examples above.
During In-band attacks, hackers can see the results of their constructed queries on the same screen as their input. (For example, on a product search page.)
They can use data that appears and different error messages to gather the information they need about the database and then construct a query to display, delete, or manipulate certain data.
Blind SQL Injection
Websites that display data returned from the database aren’t the only ones potentially vulnerable to an attack.
In a blind SQL injection, the hacker can not necessarily see the results of the queries run via his or her inputs, but can slowly piece together information based on failed pages, response times, and other subtle pieces of data that appear over the course of several queries.
In blind SQL injections, the hacker can find out:
- If the system is vulnerable
- What version of MySQL the database is running
- And more
They can use this information to construct harmful queries.
Out-of-band SQL Injection
When clues from blind SQL injections are too hard to read, attackers can sometimes force the database to send a DNS request to their own site.
If the DNS request comes through on the other end (on the hacker’s site, where they can monitor requests and activity), it gives them a little bit more information about potential vulnerabilities that they can use to further their attack.
How to Prevent SQL Injection Attacks
Your website or web app will be mostly safe if you use up-to-date technologies like WordPress or other modern, trusted content management systems.
However, it’s important to run frequent updates of your website platform (to patch vulnerabilities) and only use add-ons or plugins from the most trusted sources.
(Even a small vulnerability in a third part WordPress plugin could bypass WordPress’ built in protections.)
If you’re building something more custom or just want to know the ins-and-outs of keeping your site secure, here are a few things you can do to protect against SQL injections:
Run Frequent Updates
If you’re using a popular content management system or website platform, update it frequently. But also be sure to update your versions of PHP, MySQL, and anything else that might connect to your database.
Restrict & Sanitize Inputs
Good input fields won’t run a query unless the input specifically matches requirements you’ve set. This is called restricting your inputs.
For example, you could make sure that a username is only letters and numbers (no special characters or quotations), or that a credit card number is only numeric before submitting the query.
You can also add functionality to user inputs that recognizes dangerous characters (like quote marks) and comments them out, rendering them harmless.
Storing Sensitive Data Securely (Password Hashing and More)
Psyomjesus [CC BY-SA 4.0]
Your database should never store things like user passwords as plain text.
At a bare minimum, passwords should be hashed (or converted to complex strings of numbers and letters) using the toughest algorithms. Better yet, they should include salts (a random string of characters appended to each password before hashing) and other forms of encryption when possible.
Credit card numbers must also be encrypted or tokenized with help from your payment processor.
Failing to securely store sensitive data can be a crime in some cases.
Prepared Statements, Parameterized Queries, and PHP Data Objects
Depending on the structure of your database and which database management system you’re using (MySQL, Oracle, SQL Server, etc.) the method may change, but you’ll always want to ensure that SQL elements in a user input field are never treated as actual queries.
In a prepared statement or parameterized query, it’s a bit like pre-running the query with placeholder data in the input field, so the server knows what command to expect. The query can then not be altered outside of the input variables (so a SELECT query could not be changed to or added to a DROP or other command).
Newer versions of PHP allow you to set user inputs as PHP data objects, or PDOs. This is an extremely simple way of ensuring that the server reads any user input only as data, and will not run any commands that may be part of the input.
Do Not Display Specific Error Messages to Users
Hackers can gleam a lot of information from the errors that result from failed queries.
If anything, you’ll want to display only the most generic error message possible for all failures so as to give few clues about any potential vulnerabilities.
It’s a good idea, however, to keep a server-side log of all errors and failed requests so you can review them later in the case of an attack.
Use a Scanner to Detect SQL Vulnerabilities
There are lots of free and paid tools available online that will scan your website or app for vulnerabilities.
They’ll search for known weaknesses in your platform and test different kinds of SQL injection attacks on your site.
It’s better to know now and fix the problem then find out too late!
In 2020, SQL injection attacks should be relatively easy to avoid if you’re relying on modern and trusted technology.
However, the best practices above will help you be aware of potential vulnerabilities on an existing platform or while you’re building something more custom.
Protections are relatively easy to implement, but one slip-up could have disastrous consequences for your business, customers, or website.