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