SQL injection attacks: A cheat sheet for business pros

SQL injection has been a major security risk since the early days of the internet. Find out what's at risk, and how cybersecurity pros can defend their organizations.

What is a SQL injection attack? If your website uses a SQL database you need to be aware of injection attacks, which are simple and incredibly devastating.

Few things terrify IT security professionals—and the organizations they protect—as much as data theft. In a single moment, a company's private data, customer records, employee IDs, and myriad other types of confidential data can be lifted from internal servers. A moment more, and that data is available for sale to the highest bidder.

One of the most common methods of stealing sensitive data is SQL injection (SQLi), which targets security vulnerabilities in web applications in order to inject a malicious SQL statement into the database that stores the web app's records.

SQL databases store critical information, and despite that fact, many websites remain vulnerable to SQLi attacks, like those that target SQL, which remain the most critical web app security risk.

It's essential that not only IT security professionals, but also the decision makers they protect, understand the risk of this security threat.

SEE: A winning strategy for cybersecurity (ZDNet/TechRepublic special feature) | Download the PDF version (TechRepublic)

What are SQL injection attacks?

Structured Query Language, or SQL, is a method of managing relational databases that was first conceived of in the 1970s. Since then, it has become the standard in database management systems (DBMS) and can be found in countless organizations around the world.

When the rise of internet web applications that connected to SQL databases became commonplace, it took no time at all for SQL injection attacks to become reality. Since first being discovered in 1998, SQLi has been the bane of almost every organization with a data-driven web app.

SQLi works, at least on the surface, in a very straightforward manner: An attacker submits a malicious SQL statement in a fillable field that exploits a vulnerability in the web app's SQL implementation.

If successful, the malicious SQL statement could dump the entire contents of a database, or select data like customer records, employee ID/password combinations, or anything else the targeted database contains. SQLi can also give an attacker administrator access to a database, allowing them to delete or modify data.

Depending on the nature of the SQL database, an SQLi attack can even give an attacker access to the operating system of the machine that hosts it, which can allow the attacker to gain access to other network resources.

SEE: All of TechRepublic's cheat sheets and smart person's guides

SQL injections typically come in one of three forms: Classic SQLi (aka in-band SQLi), blind SQLi (aka Inference SQLi), and out-of-band (OOB) SQLi (aka DMS-specific SQLi).

Classic SQLi attacks are the most common and simplest form of SQLi. Classic attacks can occur whenever an SQL database allows users to submit an SQL statement. They come in two varieties:

  • Error-based SQLi, which involves getting a web app to throw an SQL error that gives the attacker either information about the structure of the database or the particular information they're seeking.
  • UNION-based attacks, which use the SQL UNION operator to determine specifics of the database's structure in order to extract information.

Blind SQLi attacks can be one of several different types of attacks, like error-based or UNION-based attacks, with the major difference being that the attacker isn't presented with an error message or any other kind of text to indicate the success or failure of their attack.

Blind attacks do, however, cause a web app to behave in different ways, depending on how the database responds to the SQL query. Attackers can then infer the structure of the database based on how the web app responds, allowing them to build a copy of the database character by character.

Because blind attacks reconstruct a database one character at a time, they are considered incredibly time consuming. The larger the database, the slower the response, which can make blind attacks impractical in many situations.

Out-of-band SQLi is a much less common approach to attacking an SQL server. It relies on certain features of an SQL database to be enabled; if those features aren't, the OOB attack won't succeed.

OOB attacks involve submitting a DNS or HTTP query to the SQL server that contains an SQL statement. If successful, the OOB attack can escalate user privileges, transmit database contents, and generally do the same things other forms of SQLi attacks do.

A fourth type of SQLi attack exists, but it was excluded from the list above because it involves combining SQLi attacks with other cyberattacks.

Called compound SQLi, these attacks involve using SQLi alongside cross-site scripting, denial of service, DNS hijacking, or insufficient authentication attacks. Pairing SQLi with other methods of attack gives hackers additional ways to avoid detection and circumvent security systems.

The end result of all these different forms of SQLi is the same: The theft of sensitive data that can not only endanger an organization, its employees, and its customers, but also erode trust in that organization's ability to keep confidential data safe.

Additional resources

Who is at risk for an SQL injection attack, and what can happen to victims?

Organizational risk for an SQLi attack only requires meeting two simple criteria: Have a website, and having that website interact with an SQL database.

Without proper security in place, and in light of reports that some industries face over 1,000 cyberattacks a day, it's easy to imagine falling victim to an SQLi attack.

While any website relying on an SQL database can be subject to SQLi attacks, data-driven web apps are particularly tempting targets to hackers because of all the information they collect and store.

A data-driven web application is any app that changes its behavior based on the data a user inputs. Examples of data-driven applications include:

  • Survey apps;
  • report-generating apps;
  • search apps;
  • guest books;
  • workflow applications;
  • apps that generate notifications/reminders; and
  • social media websites.

All of those types of web applications—and more—are potentially at a higher risk of SQLi attack.

With all of that data potentially up for grabs, it's easy to see what can become of organizations that suffer an SQLi attack. In particular, effects from successful SQL injection can include:

  • An attacker creating a copy of the entire database (or specific portions);
  • an attacker spoofing login credentials, impersonating a user, or even bypassing authentication entirely;
  • modification of the database (e.g., changing, deleting, or adding records);
  • theft of non-database files located in the DMBS' file system;
  • execution of operating system commands that give an attacker access to other assets on the network that hosts the SQL database; and
  • knocking the target web application/DBMS offline.

Those direct results of a successful SQLi attack are big enough problems on their own, but they aren't the only thing to consider—there's also the long-term fallout. Sale of confidential data on the dark web, loss of customers, recovery costs, erosion of trust—all of those outcomes and more can be the result of a successful SQLi attack.

Additional resources

What are examples of successful SQL injection attacks?

SQL injection attacks have been plaguing the internet for over 20 years; in that time, many high-profile attacks and vulnerability discoveries have occurred.

Additional resources

How can I prevent an SQL injection attack against my organization?

Make no mistake—SQL injection is incredibly dangerous and surprisingly commonplace. Fortunately, protecting your website or web app against SQLi isn't difficult to do.

For starters, use an SQLi probing tool like Tyrant-SQL to find any vulnerabilities your site or app may have. It can be difficult to narrow down what you're at risk for and running an app like Tyrant can make finding weaknesses much easier. Doing this initial step isn't strictly necessary, but it can give you a better picture of what you're up against.

SEE: How to build a successful career in cybersecurity (free PDF) (TechRepublic)

As for protection steps, OWASP's SQLi prevention page has an excellent rundown of how to defend yourself (as well as more details and examples). Note: These strategies can be used to protect other databases, like XPath and XQuery, as well. Here's an overview of what OWASP recommends.

1. Use prepared statements instead of dynamic queries

Prepared statements with parameterized queries should be how all database queries are written. Parameterization means that all SQL code involved in the query has to be defined beforehand, which means the database will be able to distinguish between code and user input.

If an attacker tries to input a malicious SQL statement, the database will treat the statement as data, not code, and the query won't be turned into a malicious one.

2. Use stored procedures

Using stored procedures is similar to using prepared statements—at least from the standpoint that both involve defining valid SQL statements beforehand. The difference between stored procedures and prepared statements is where the SQL code lives: In the case of prepared statements, SQL code is stored in the web app itself, whereas stored procedures live in the database and are called by the web app.

There are instances where stored procedures can be unsafe, particularly if dynamic SQL generation is used inside stored procedures. Avoid this if possible; if dynamic SQL generation is necessary, be sure that stored procedures are using input validation or proper escaping to prevent the injection of malicious code.

Stored procedures aren't as foolproof as prepared statements. Whenever possible, opt for prepared statements over stored procedures.

SEE: Cybersecurity and cyberwar: More must-read coverage (TechRepublic on Flipboard)

3. Use input validation

In cases where some SQL code is a necessary part of user input, it's essential to create a whitelist of valid SQL statements. Only create a list of the most essential statements to avoid unvalidated statements ending up in a query.

Input validation alone isn't enough to protect an SQL database, but it should be used in all cases as an additional layer of protection.

4. Escape all user-supplied input

Escaping SQL input has the effect of eliminating the threat posed by certain characters in SQL statements. Depending on the kind of escape function used, SQL input statements will be edited to prepend a "\" or other character in front of certain characters in order to prevent malicious commands from executing correctly.

This is considered a last-ditch method of preventing SQLi attacks. By itself, escaping isn't reliable and is prone to error, especially if an escape function isn't coded correctly. Only use escaping if other prevention methods aren't possible.

SEE: Intrusion detection policy (Tech Pro Research)

Additional defense methods

OWASP also recommends limiting database account privileges, especially for application accounts. While granting admin access to these accounts can make an application run smoothly, it also opens the door for SQLi by giving users admin access to the database.

In addition, it can be helpful to use different user accounts for different web applications in order to limit what a particular account has access to. Further, limiting read access to specific fields can prevent a successful SQLi attack from extracting information, leaving an attacker with only a small piece of database contents that render it useless.

Additional resources

Image: ValeryBrozhinsky, Getty Images/iStockphoto

By Brandon Vigliarolo

Brandon writes about apps and software for TechRepublic. He's an award-winning feature writer who previously worked as an IT professional and served as an MP in the US Army.