An SQL Injection – or SQLI is a type of cyber security attack that targets application security weakness and allows attackers to gain control of an application’s database. An attacker inputs a malicious input into an SQL statement., and the SQL server reads it as programming code.
Some SQLI attacks can release lists of sensitive customer data while others delete part (or all) of a database. And some can even remotely run software applications.
SQL Injection attacks are relatively easy and commonplace. Preventing and detecting SQLI attacks is a necessary aspect of security diligence.
How To Prevent SQL Injection
Most experts agree that the best solution is to prevent intrusions before they happen. This can be done by upgrading security combined with vulnerability scanning and penetration testing. Consider using a third party service to check your website for known weaknesses.
Sanitize Input
Also known as validation; this checks the input before it’s executed. This is sort of like putting a bouncer at the front door. An additionally hidden program checks to make sure the input doesn’t contain any SQL executables. The best practice in this solution is to use a whitelist for approved input. Using a whitelist is simpler and more secure than blacklisting unauthorized code.
Parameterized SQL Code
This creates a sort of storage area (a parameter, or a prepared statement) that holds the user input. If an input doesn’t match the type for the parameter, it’s ignored. This technique prevents direct access between a user and the database. Since the user cannot issue commands directly, they cannot inject malicious SQL code.
Use Object-Relational Mapping
ORM libraries are tools for translating between programming languages. This may take help from developers, but it creates a system of virtual objects that run specific SQL queries. Like the previous step, this removes the ability of users to query the SQL server directly.
Limit User Permissions
This is a best practice in all IT and server administration. It holds especially true to preventing SQLI attacks. Simply limit users to the bare minimum permissions they require — the fewer accounts with read-write-execute permissions, the fewer opportunities for exploitation.
Third-Party Security Software
Web Application Firewall software can screen SQL inputs. There are many third-party security tools available. If you don’t have programming skills (or a developer team), consider using software to screen SQL inputs.
How to Detect SQL Injection Attacks
SQLI attacks often look like standard database errors. Without special tools, they can be difficult to detect in real time. An SQLI attack usually involves trial and error. Sometimes, a worm (or bot) repeatedly probes your website for flaws. Sometimes a human hacker enters SQL codes into your website. These attacks don’t always require a login, making detection much more difficult.
Detecting an SQLI attack while it’s in progress requires additional configuration or third-party tools. One method is to examine sqlserver.error_reported event for specific errors. Frequent failed login and bad syntax errors might indicate repeated intrusion attempts. Alternately, the database can be searched for common HTML tags, such as “iframe” or “http-equiv=”refresh.”
A second method to identify an SQLI attack involves traffic analysis. This requires a third party monitoring tool. Over time, the monitor will establish a baseline of standard behavior. If there’s a suspicious change, like changes in permissions, the monitor can trigger an alert.
Auditing software can help after a breach. These tools examine error logs looking for damage that was done. This data can be used to improve security and prevent the next attack.
Typically, these tools monitor:
- Repeated failed logins – These can indicate trial-and-error to bypass SQL security.
- Password changes – Unexpected password changes might indicate a hacker gained access to a user.
- Permission and Ownership changes – Files modified in this way can be evidence that a hacker made the changes to grant themselves access.
- Logins, Logouts, Database operations – While not inherently a sign of intrusion, unusual or unexpected account activity can indicate a breach.
How SQL an Injection Attack Works?
To understand an SQLI attack, it’s helpful to understand an SQL query.
There are two layers to an SQL query on a webpage. The first is user input, like a username and password field. The second is where the hidden programming code creates a SQL query against the database.
A SQL injection attack is when an attacker puts SQL code in a box designed for regular input. This fools an unprotected system into running the string of characters as a programming code.
A simple example works like this:
An attacker decides to attack a SQL server. The server has a prompt for a username and password:
Enter Username: username
Enter Password: password
The server has a database with a table of usernames and passwords. When a user enters their information, the server compares them against the table of usernames and passwords. If the server finds a match for both, the user is granted access.
The hidden program translates the user’s input into a query. A SQL query asks the database for a specific set of information.
A hidden SQL query might look like this:
SELECT UserList.Username
FROM UserList
WHERE UserList.Username = ‘username’
AND UserList.Password = ‘password’
The SQL server is running a query on the UserList table in the database. It’s looking for an entry that matches the ‘username’ input that was entered by the user. But that’s not enough – the database entry also has to contain the ‘password’ input from the user. If both match, the database returns a TRUE result, and the user gains access.
With an SQLI attack, the SQL query can be hacked.
An attacker might enter the following:
Username: username
Enter Password: password’ OR ‘1’=’1
This injects a piece of code into the query. Now, the hidden program creates a database query like this:
SELECT UserList.Username
FROM UserList
WHERE UserList.Username = ‘username’
AND UserList.Password = ‘password’ OR ‘1’=’1’
Note how the last line changed. The contents of the Enter Password field were added to the query. The SQL database reads this as executable code. Now, it’s being asked to check the database whether the number 1is equal to the number 1. It doesn’t matter if the username and password are correct. The formula “1=1” is true, so the database returns a TRUE result, and the user is granted access.
SQL Injection Example
One popular command can be used to delete entire tables from a database:
Enter Username: ‘;DROP TABLE User; ––‘
Enter Password: ‘OR”=’
Once the command is turned into a query, it looks like this:
SELECT UserList.Username
FROM UserList
WHERE UserList.Username = ‘ ‘; DROP TABLE User; ––‘AND Pwd = ‘ ‘ OR”=’
The attacker has injected a piece of code that executes DROP TABLE against the UserList table. Then, the rest of the line is marked as a comment, so it doesn’t run. This would delete the whole UserList table from your database.
SQLI attacks can also be used to run multiple commands. The UNION command can be used to run more than one SELECT:
Enter Username: username
Enter Password: password UNION SELECT Username, Password FROM UserList;
The hidden program would query the database as follows:
SELECT UserList.Username
FROM UserList
WHERE UserList.Username = ‘username’
AND UserList.Password = ‘password’ UNION SELECT Username, Password FROM UserList;
This would return a list of all the usernames and passwords from the table UserList.
This type of attack can wreak havoc.
It can result in data breaches if unauthorized users gain access to user data. It can be used to destroy whole portions of your database. It can even be used to launch and execute malicious software on your server.
SQL Injection Prevention Starts With Being Prepared
SQL Injection attacks don’t require a lot of skill to cause massive damage. Anyone with a computer can look up a SQL exploit and run it against your server, making it a tempting attack vector. Fortunately, they are relatively simple to defend against.
Locking down your SQL server to prevent injection attacks is an important step. It’s a lot easier to prevent an attack than to restore from backup. (Or to notify a client list of a security breach.) Take a moment to evaluate your SQL code.
The Open Web Application Security Project (OWASP) maintains an excellent list of resources and cheat sheets for protecting your SQL servers.
Schedule a security audit. Then sit down with your development team to implement SQLI protection on production servers.