Structured query language (SQL) is a standard computer language for creating, editing, and accessing relational databases. Developed by IBM in the 1970s, it’s commonly used by database administrators to run tasks and execute commands.
The vast amounts of information stored in databases makes them valuable targets for hackers, who can exploit inherent characteristics of how SQL functions to trick a database into granting them access even without a valid login. These attacks, called SQL injections, can be costly—reported losses from some attacks have reached $300 million. Others have resulted in data breaches affecting the personal records of almost an entire country.
These examples illustrate why it’s imperative that managed services providers (MSPs) know how to prevent SQL injection attacks that use malicious code with devastating consequences. This piece will walk you through a number of SQL injection examples so your team has a better idea of what to look out for to keep your customers’ databases secure.
How does a SQL query work?
In order to understand why SQL injection attacks are so pernicious, it is helpful to first walk through how a standard SQL query works.
One fundamental SQL command is the SELECT statement. When querying a database, SELECT allows you to retrieve data based on certain provided parameters. For instance, if a customer shopping on an e-commerce store wanted to see an item description, the SQL query might look something like this:
SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = ItemNumber
From this, the store’s web application strings together the different variables into a single SQL statement that goes to the database:
sql_query= " SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = " & Request.QueryString("ItemID")
The application retrieves the item name and description based on the item number value provided, then displays the information to the customer.
What is an SQL injection?
An SQL injection is a common hacking technique that involves placing malicious code within improperly formatted SQL queries. This occurs when users are asked to input information, such as usernames—only instead of providing a username, a hacker inputs an SQL statement designed to run surreptitiously. This technique allows them to access, edit, and potentially even delete a database.
Usually, there are two parts to an SQL injection attack. The first step is to research in order to determine how to effectively trick the target database. An attacker will try inputting unexpected values for the argument in the SQL statement, which can reveal vulnerabilities in the database queries. The attacker then uses the application’s responses—including the information provided in error messages—to formulate an SQL command that tricks the database.
From there, the hacker will go in for the attack. Based on the observations determined in the research step, the hacker enters an input value which the database interprets to be an SQL command rather than data. The database then runs the command.
There are a number of tools available that allow hackers to automate both the research and attack portions of an SQL injection, which means it’s vital to maintain strong and effective security protocols to prevent and protect against SQL injections.
Examples of SQL injection attacks
Let’s return to the e-commerce example from earlier, which retrieves an item description based on a given item number. A hacker executing an attack could conceivably enter an input value like the following:
ItemNumber: 105 OR 1=1
Then, the SQL statement will look like this:
SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = 105 OR 1=1
The addition of OR 1=1—a statement that the database will recognize as always being true—has the unintended effect of returning every product name and description in the database, even the ones that shoppers may not normally be allowed to access.
Here’s another SQL injection attack example that allows hackers to circumnavigate login credentials. When presented with a login field, a hacker might enter the following values:
Username: " OR ""=" Password: " OR ""="
The result will be another valid SQL statement. Because the database recognizes » OR «»=»» as always being true, it will return all values for the username table, giving the hacker access to everyone’s login information. Here’s one last—and particularly dangerous— example:
SELECT ItemName, ItemDescription FROM Items WHERE ItemNumber = 105; DROP TABLE USERS
This particular statement uses the semicolon, which can be improperly filtered by a database, to create a command that has the potential to delete the entire user database.
There are many more ways that SQL injection attacks can be disruptive, but the threat illustrated by these basic examples is obvious, especially when it concerns database tables containing sensitive client information. That’s why it’s incredibly important for MSPs and database administrators to have a solid grasp and understanding on how to properly format each part of an SQL query.
For more information on SQL injection and other common threats, read through our related blog articles.