How this SQL injection works? Explanation needed

Question

I'm learning about RoR/databases and this topic particularly confused me. In the book Agile Development with Rails 4 , they give an example of finding a list of all orders with for an entry with name Dave:

pos = Order.where("name = 'Dave' and pay_type = 'po")

The book goes on to say that you would never want to do something like this:

name = params[:name]
pos = Order.where("name = '#{name}'and pay_type = 'po'")

Instead you should do this:

name = params[:name]
pos = Order.where(["name = ? and pay_type = 'po'",name])

I understand, what SQL injection is as a concept but there are a few particulars that confuse me. For starters, how exactly does the SQL injection work as a syntax.

I get that the danger is that someone can drop a table/database if you interpolate an outside form parameter like the first example but how?

Lets say you had this:

name = params[:name] #DROP DATABASE database_name
pos = Order.where("name = '#{DROP DATABASE database_name}'and pay_type = 'po'")

Is this how SQL injection works? SQL is a syntax, there should be no field in the database where "name = DROP DATABASE database_name", wouldn't this return an error instead of dropping the database?

Also, how would the question mark version protect against this. Again, let's say you have this scenario.

name = params[:name] #DROP DATABASE database_name
pos = Order.where(["name = ? and pay_type = 'po'", DROP DATABASE database_name])

Won't this replace the question mark with the DROP DATABASE database_name syntax and then wouldn't we have the same problem that we had in the first example? How exactly is this protecting an application against SQL? I searched for a few tutorials on http://hub.tutsplus.com/ and searched on Google, but I don't get the concept behind it. Any help?

Answer 1

The simplest Explanation i can give for what SQL injection is:

This may make a SQL query like the following:

SELECT * FROM Order WHERE name = 'Dan' AND pay_type = 'po'

Now a nice user would supply the name Dan like above.

But an evil user (let's call him Bobby), would supply the name: Bobby Tables'; DROP DATABASE master; --

That creates a query like:

SELECT * FROM Order WHERE name = 'Bobby Tables'; DROP DATABASE master; --' AND pay_type = 'po'

which effectively executes the two queries:

SELECT *
FROM Order
WHERE name = 'Bobby Tables';

DROP DATABASE master;

And now the database is gone. Worse damage comes from when they pull private information out of the database instead (like username/passwords or credit card info)

---

As for why the question mark magically now protects you:

Using the question mark in RoR, makes use of a pattern called parameterization. When you parameterize a SQL query, you write it in such a way that it prevents anyone from entering a successful SQL injection. Everywhere a question mark is used, it is replaced by a parameter. That parameter is then safely set to a value at the top of the query by escaping any quotations.

If you now supply the name Dan to:

Order.where(["name = ? and pay_type = 'po'", params[:name])

the query would look something like: (RoR may parameterize slightly differently internally, but the effect is the same)

DECLARE @p0 nvarchar(4000) = N'po',
        @p1 nvarchar(4000) = N'Dan';

SELECT [t0].[ID], [t0].[name], [t0].[pay_type]
FROM Order AS [t0]
WHERE ([t0].[name] = @p1) AND ([t0].[pay_type] = @p1) 

And now if evil Bobby comes along with his name of: `Bobby Tables'; DROP DATABASE master; --

if would parameterize (and escape quotations) the query like:

DECLARE @p0 nvarchar(4000) = N'po',
        @p1 nvarchar(4000) = N'Bobby Tables''; DROP DATABASE master; --';

SELECT [t0].[ID], [t0].[name], [t0].[pay_type]
FROM Order AS [t0]
WHERE ([t0].[name] = @p1) AND ([t0].[pay_type] = @p1) 

That is now a perfectly safe query

Hope that helps you understand

Answer 2

It has to do with how the code interpreter works.

In the first example, the parameter is simply inserted as text, and then the entire command is processed. Hence, problems.

In the second example, the command is interpreted first, and then the parameter is inserted afterwards. (IE, it interprets "do statement where name=[some parameter]", and then after it does that, adds the parameter.) So all you'd get would be a very weird equality where name = "); drop table blah;" which of course wouldn't work unless you have some weird names in your data.

Note, the injection has to actually properly end your command and start a new one - otherwise it would just cause an error.

Answer 3

This might seem like a minor quibble, but it has enormous implications if you fail to get this right. Discipline is extremely important. If you forget this, you will have a bad day because of some very nasty things people can do once they find a vulnerability.

Every day there's another example of how a tiny little mistake caused by not taking things seriously from the start causes serious problems. Today's example: 4chan gets hacked due to a single parameter not being escaped. That's all it takes. One mistake.

Whenever possible, use SQL placeholders for values. DO NOT take shortcuts. The risk is way too high.

ActiveRecord has many methods for composing queries that don't involve manipulating the query directly:

Order.where(name: 'name', pay_type: 'po')

Whenever possible, use these. If you're coming up against a limitation, do it as safely as possible:

Order.where([ 'name LIKE ?', "%#{name}" ])

You can also use the escaping feature more directly:

Order.sanitize(name)

If you must introduce some arbitrary text in your query, make sure it's throughly validated. Always make a very narrow list of accepted parameters. Don't make the mistake of adding a few rules for exclusion which leaves open the possibility you forgot one. It's far better tone too strict than too lax.

With Rails you're on a pretty good foundation. Don't screw it up. One way to avoid a lot of this mess is to use single-quoted strings so interpolation is off-limits:

Order.where('name="#{name}"') # Won't work, isn't interpolated.

That should, if made a habit, make you very hesitant to switch to interpolated strings.

If you're on top of all this, you need to keep in mind that exactly the same patterns play out in the HTML space where you need to be aware of XSS and HTML injection issues that require the same level of discipline. In modern Rails all user data is automatically HTML-escaped in views, but that doesn't always help with embedded JavaScript. Whenever you have questions, ask if what you're doing is secure or not to be sure.

Answer 4

There has been much written on this, so I am not going to go into it, but to answer your question, the hacker, depending on the database, can issue a GO and then drop database OR ;DROP DATABASE, etc. As Heartbleed has taught us, it is even more damaging if you don't know and they could do something like "99;select * from master" to get sensitive info. To protect, you are mostly guided to use stored procedures WITH parameters to let the DBMS validate AND never string things together as dynamic SQL unless you really have to. It is dangerous and performance is almost always affected negatively.