Feature
If your eyes glazed over at the recent announcement of an “SQL injection” vulnerability in WordPress, take heart. You’re not alone.
SQL injection attacks are a common kind of security flaw, but are subtle enough that it’s hard for regular folks to understand what’s at risk.
In this article we’ll walk you through the basics: what “SQL injection” means, what risk it presents to you, and what you can do about it.
SQL and Databases
SQL (short for “Structured Query Language”) is the lingua franca of relational databases. It’s the standardized way across the world to write queries that retrieve information from a large, complex database. Oracle, SQL Server, MySQL, Postgres, Sybase, Informix — programmers use the same language to retrieve data from all of these systems.
SQL queries are written in an English-like syntax. Imagine a magazine publisher which keeps a list of subscribers in a database table, sorted by first name, last name, address, subscription expiration date and probably a bunch of other data.
The magazine’s intranet might let users look up a subscriber’s address in the database by filling out a FIRST_NAME field and LAST_NAME field and then issuing this query:
SELECT address, city, zip_code FROM subscribers WHERE first_name = "$FIRST_NAME" AND last_name = "$LAST_NAME"
When the user enters “John” for the FIRST_NAME field and “Doe” for the LAST_NAME field, the server substitutes those names in the SQL command and returns all of the addresses for subscribers named John Doe.
Lethal Injection
There’s a very serious problem with the above example. A clever user will notice that the contents of the FIRST_NAME and LAST_NAME fields get copied into the SQL code itself.
What happens if a user doesn’t enter a name in that field, but actually inserts some SQL code of their own? Look at the command that gets issued if the user enters this odd-looking string for the LAST_NAME field:
Doe" OR 1=1
That causes the server to issue this query:
SELECT address, city, zip_code FROM subscribers WHERE first_name = "John" AND last_name = "Doe" OR 1=1
That instructs the server to retrieve all fields from the database where either the subscriber’s name is “John Doe” or where the equation “1=1” is true—and since that’s true no matter what, it’ll return a list of every subscriber in the database.
That’s why this sort of attack is known as SQL injection: the user’s input is injected into code that the computer runs. If the server hasn’t been careful to “sanitize” this input, then it can be tricked into running code that the author never put there in the first place, and bypasses any security protection.
Learning Opportunities
Webinar
Apr
30
Supercharge Contact Center Efficiency with Generative AI
Learn practical strategies to reduce post-call documentation time by over 8 minutes.
Register
Conference
May
1
Qualtrics X4 Salt Lake City 2024
Register
Webinar
May
2
Unleash AI for Crisis Management: Transform Threats into Triumphs
Learn how Sprinklr Insights provides real-time insights on current events, trends, and brand crises to empower your team.
Register
Webinar
May
9
Driving Customer-Centricity in the Financial Services Industry
Discover how financial organizations are leveraging generative AI technologies.
Register
Conference
May
9
An Irish Roundtable in the Valley: The Economic Impact of AI on the World of Work Stanford 2024
Exploring the Impact of AI On the World of Work with Irish diaspora leaders and tech business champions from the North of Ireland
Register
Conference
May
13
Gartner Marketing Symposium/Xpo Conference London 2024
Register
Here’s a more destructive example. The SQL command “DROP TABLE” instructs a database server to delete an entire table of data. By adding a semicolon (“;”) into the injected code, an attacker could trick the server into running multiple SQL commands:
SELECT address, city, zip_code FROM subscribers WHERE first_name = "John" AND last_name = "Doe" ; DROP TABLE subscribers
and just like that, the server returns the subscriber record for John Doe … small consolation, though, because it also subsequently deletes all of the subscriber records.
(This sort of attack is popularly known among geeks as “Little Bobby Tables,” after the XKCD cartoon Exploits of a Mom. The cartoon, a panel of which is shown at left, introduced readers to a security engineer and her son, Little Bobby Tables.
The lesson of the cartoon is, of course, to always sanitize your database inputs. There’s even a website dedicated entirely to helping people fix SQL injection attacks — named, of course, bobby-tables.com.)
SQL injection attacks allow a malicious user potentially unlimited access to the content in your application’s database, no matter what security the application has tried to enforce.
A vandal could destroy the database contents and leave you scrambling for backups. A thief could obtain any sensitive data from the database they like, from users’ home phone numbers to their credit card accounts.
What Can You Do?
The bad news is that SQL injection attacks are very common. The good news is that they’re almost always easy to fix. Securing an application against SQL injection is a matter of making sure that every SQL query has been “sanitized” so that user-submitted data can’t be executed as code, and sanitizing data is a solid, well-understood process.
Site owners and administrators should:
- Stay up-to-date on security releases for public-facing software. Once a security alert has been issued for your CMS, you should assume that attackers are trying to exploit that vulnerability right now, and make the security upgrade a priority.
- Wherever possible, don’t use a privileged account to access the application database. If the application gives you a way to run without access to a database administrator account, make sure you’ve done that. Applying the principle of least privilege means that you’ll at least minimize the damage that can be done if someone does exploit a vulnerability.
Programmers should:
- Get to know techniques for avoiding injection attacks. It’s not hard. It just requires taking a little more care when writing database queries.
Just a little bit of diligence will help you avoid becoming the next Bobby Tables — and losing customer confidence in the process.
Title image by stevendepolo.
