How does this SQL injection work? Clarification required

Question

I learn about RoR / databases, and this topic particularly confused me. In an Agile Development book with Rails 4, they provide an example of finding a list of all orders for an entry named Dave:

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

The book goes on to say that you'll 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 that SQL injection is a concept, but there are a few details that confuse me. First, how exactly does SQL injection work as syntax.

I understand that the danger is that someone can delete the table / database if you interpolate the external form parameter as the first example, but how?

Let's say you had this:

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

SQL-? SQL , , "name = DROP DATABASE database_name", , ?

, . , , .

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

DROP DATABASE, , ? SQL? http://hub.tutsplus.com/ Google, . ?

Answer 1

, SQL-:

SQL- :

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

Dan, .

( ), : Bobby Tables'; DROP DATABASE master; --

, :

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

:

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

DROP DATABASE master;

. - , (, / )

---

, :

RoR, , . SQL-, , - SQL-. , . , .

Dan:

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

: (RoR - , )

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) 

, : `Bobby Tables '; DROP DATABASE; -

( ) :

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) 

,

Answer 2

, .

, . , .

, . (IE, do, name= [ ] ", .) , , , , name =" ); drop table ;" , , , - .

, , .

Answer 3

, , . . , - , , .

, , , . : 4chan - , . , . .

SQL- . . .

ActiveRecord , :

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

. , :

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

:

Order.sanitize(name)

, , . . , , . , .

Rails . . - , :

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

, , , .

, , HTML, XSS HTML-, . Rails HTML , JavaScript. , , , , , .

Answer 4

, , , , , , GO, OR, DROP DATABASE .. Heartbleed , , , - "99, * ", . , , SQL, . , .