Avatar
GuestNo new alerts

What is a SQL Injection?

- Articles2855
Avatar
You may already know what this is, but if you don't, then you may have heard this phrase before. SQL Injections are sadly, one of the leading ways in which websites are compromised, especially ones which were built back in 2007 and never modernised.

Before we can really understand what a SQL Injection is, we need to understand what SQL is. SQL is a language which you use to talk to the database, tell it what to do, and tell it which bits of data to bring back.

As someone funnily said to me before, a database is essentially a box filled with spreadsheets, although in reality, it's a bit more complex than that.

Let's assume that we have a page say... /topics/?id=45 which you pass an ID to (or more likely, some sort of index passes an ID for) and it loads for the corresponding topic for that ID.

rows, err := db.Query("SELECT tid, content FROM topics WHERE tid = " + r.PostFormValue( "id" ))
if err != nil {
// Do something if the query failed
}


And let's assume that to load that, we use this horrible, horrible code you should never use. That line would retrieve a set of rows containing the tid and the content of the topic for you to populate your topic page with.

However!

There is one fatal flaw in that code. You should never trust input from the user, this sort of mistake can be ripped apart by a random bot within a week. It's actually a miracle that some of the companies with this flaw last for so long.

Here's why. When you concatenate, you don't pass parameters to the query, but rather. You modify the structure of the query itself by adding a bit onto the end. These may seem similar, but are fundamentally different.

If we look at it in a darker more sinister point of view, it allows one to do something like this:

/topics/?id=42; UPDATE users SET admin = 1 WHERE uid = 52;


Let's imagine that the attacker has the uid 52, perhaps they've randomly signed up and started prodding around and that you have an admin column to denote who has administrator priviledges. Just like that, they've gained admin access on your site. Horrifying.

There are even tricks to squeeze out usernames and passwords. For instance, and this is stretching this particular example, but it would work better with a comments section of some sort or replies. But. You can basically do this:

/topics/?id=42; SELECT uid, password FROM users WHERE uid 50;


This won't work on modern systems, as most have the foresight to not store passwords in plaintext, but it shows what you can do with such a simple oversight. It's a truly horrifying vulnerability.

The solution to this is prepared statements which in the previous case would be:

rows, err := db.Query("SELECT tid, content FROM topics WHERE tid = ?", r.PostFormValue( "id" ))
if err != nil {
// Do something if the query failed
}


In this example, the id is passed to the database as a parameter separate from the query. No matter what we throw in there, it cannot affect the structure of the query, a beautiful thing to behold. But you shouldn't stop there, as we're expecting a number, we should force it to be one. In this example, it would be:

tid, err := strconv.Atoi(r.PostFormValue( "id" ))
if err != nil {
// Invalid integer!
return;
}

rows, err := db.Query("SELECT tid, content FROM topics WHERE tid = ?", tid)
if err != nil {
// Do something if the query failed
}


The name Atoi is slightly cryptic, but it's basically a function which converts strings to integers and coughs up an error, if there's a problem. Constraints are important, as weird things can happen when code gets values in forms it doesn't expect, and it helps to pin down bugs fast.

That concludes this article, I hope this was useful to you, feel free to discuss below 😉