SQL Injections & (Ideally Not) You

Before diving headlong into the magic of ActiveRecord, our class spend a few days learning the basics of SQL - practicing basic queries, joins etc. - as well as beginning a much longer discussion around data-driven applications. A topic that came up briefly piqued my interest: the strangely biological/viral-sounding ‘SQL Injection’ - a malicious action someone can take against an unprepared database to acquire information, or (sometimes) worse, destroy the data. We spent much more time discussing the ‘sterilization’ of code to prevent such vulnerabilities than the actual mechanics of an attack, so I thought it would be worth while to learn more.

Essentially all consumer software applications are based on top of databases - they’re used to keep track of user accounts, user preferences, user passwords, bank account numbers…the list goes on. At the most basic level, the way software interacts with its databases is pretty similar: when information is needed, a query (in one form or another) is sent to the database and the requested information is transmitted back. Depending on how the software is written, this query can be much more accessible than one might imagine.

An SQL Injection is a technique that leverages specific syntactic patterns found in SQL interactions with relational databases to gain access to information that would otherwise be unavailable. Such injections can allow someone to log into other peoples accounts, change or extract data, and even drop entire databases.

There are a few ways to actually perform an injection attack - a common / accessible one being via URL manipulation. A particularly vulnerable website will expose their dynamic SQL queries as you navigate their site. You can find common examples of this by googling:

  • inurl:index.php?id=
  • inurl:buy.php?category=
  • inurl:article.php?ID=
  • inurl:play_old.php?id=
  • inurl:declaration_more.php?decl_id=
  • inurl:pageid=

While not every site returned is definitely vulnerable, testing each is as easy as navigating to the page and entering an apostrophe after the url ( *http://www.example.com/index.php?catid=1%E2%80%99* ). If a site isn’t well protectected, you’ll see an SQL error on the page.

Using this information, someone with enough knowledge (and time on their hands) could determine the structure of the site’s database tables and construct queries to extract information and data.

Let’s look at a (very simple) different example: Your favorite social media website Facetagram asks you for your username and password in order to authenticate you and allow you to access/view your posts and friends. It’s quite possible that these two fields (username and password) are wired to the “Users” table of the Facetagram relational database, which means when you plug your credentials in and hit ‘enter,’ the SQL query sent might look like something similar to this:

1
2
3
4

uName = getRequestString("UserName");
uPass = getRequestString("UserPass");

sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + "'"

As you can see, the user’s entries are assigned to variables which are then directly used in the query to access the user’s data. Seeing these mechanics, someone without much SQL experience could reasonably believe that the data is safe because a user must enter in the correct password - we’ve all had to re-enter our credentials when we have a type logging into a site! However, this is not necessarily true (at least in this elementary example).

In the example outlined above, a savvy individual could enter " or "" = " into the username/password entry fields, which would create the following valid SQL query:

1

SELECT * FROM Users WHERE Name ="" or "" = "" AND Pass ="" or ""=""

Because WHERE "" = "" always evaluates as true, this SQL injection would return all rows of the Users table to the hacker, exposing the information of potentially thousands of users.

This is pretty scary stuff, given how easily (again, in this elementary example) this can be executed. However, developers can protect their code…and their databases. Using a technique called sterilization, the susceptible syntax can be substituted in the query with question marks (or other symbols, I believe) and the user-entered variables are called on at the time of execution. By passing the user entries in as such, SQL does some behind the scenes magic, encapsulating the entry into a sting, ensuring no special characters are able to affect the query…like ones that were used above. Using this method, the code example from above could be refactored into:

1
2
3
4
5
6

uName = getRequestString("UserName");
uPass = getRequestString("UserPass");

sql = "SELECT * FROM Users WHERE Name ='" + ? + "' AND Pass ='" + ? + "'"

db.Execute(sql, uName, uPass)

If our hacker tried the same SQL injection now, their entry would be rejected by the system because after sterilization it would look like this: " "" or ""=" "

Despite being preventable, SQL Injection attacks against large companies are very common. Companies such as VTech, TalkTalk, Planned Parenthood, MySQL Servers, and University of Sydney are just a handful of those hit…in 2015 alone. In the past 4 years, the likes of Yahoo, LinkedIn, Adobe, and LivingSocial have met similar fates, exposing hundreds of thousands of user accounts and passwords. A more comprehensive list (with associated hack details) can be found here.

More information on identifying vulnerable websites and google dorking can be found here. For more information about preventing SQL injection attacks, check out OWASP’s website.

flatiron school, sql