How can prepared statements protect from SQL injection attacks?

2018-06-05 06:25:16

How do prepared statements help us prevent SQL injection attacks?

Wikipedia says:

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

I cannot see the reason very well. What would be a simple explanation in easy English and some examples?

The idea is very simple - the query and the data are sent to the database server separately.
That's all.

The root of the SQL injection problem is mixing of the code and the data.

In fact, our SQL query is a legitimate program . And we are creating such a program dynamically, by adding some data on the fly. Thus, this data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it become a part of the program, so the data may alter the program and depending on the data passed, we will have either a regular output or a table users deleted.

While in case of prepared statements we don't alter our program, it remains intact
That's the point.

We are sending a program to the server first

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called a parameter or a placeholder.

Note that the very same query being sent to the server, without any data in it! And then we're sending the data with the second request, essentially separated from the query itself:

$db->execute($data);

so, it can't alter our program and do any harm.
Quite simple - isn't it?

However, it worth to be noted that not every time you're using a placeholder, it is processed as a prepared statement .

A placeholder is a general idea for substituting the actual data with a variable for the future processing (see printf() for example), while a prepared statement is the only a subset of it.

There are cases (notably PDO in PHP can do it) when a prepared statement can be emulated, and a query is actually composed along with data and sent to the server in one request. But it is important to understand that this approach is equally safe , because every bit of data is properly formatted according to it's type and therefore nothing wrong could be happen.

The only thing I have to add that always omitted in the every manual:

Prepared statements can protect only data, but can't defend the program itself .
So, once we have to add, say, a dynamical identifier - a field name, for example, prepared statements can't help us. I've explained the matter recently , so I won't repeat myself.

Here is SQL for setting up an example:

CREATE TABLE employee(name varchar, paymentType varchar, amount bigint);

INSERT INTO employee VALUES('Aaron', 'salary', 100);
INSERT INTO employee VALUES('Aaron', 'bonus', 50);
INSERT INTO employee VALUES('Bob', 'salary', 50);
INSERT INTO employee VALUES('Bob', 'bonus', 0);

The Inject class is vulnerable to SQL injection. The query is dynamically pasted together with user input. The intent of the query was to show information about Bob. Either salary or bonus, based on user input. But the malicious user manipulates the input corrupting the query by tacking on the equivalent of an 'or true' to the where clause so that everything is returned, including the information about Aaron which was supposed to be hidden.

import java.sql.*;

public class Inject {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=user&password=pwd";
        Connection conn = DriverManager.getConnection(url);

        Statement stmt = conn.createStatement();
        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='" + args[0] + "'";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

Running this, the first case is with normal usage, and the second with the malicious injection:

c:temp>java Inject salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary'
salary 50

c:temp>java Inject "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary' OR 'a'!='b'
salary 100
bonus 50
salary 50
bonus 0

You should not build your SQL statements with string concatenation of user input. Not only is it vulnerable to injection, but it has caching implications on the server as well (the statement changes, so less likely to get a SQL statement cache hit whereas the bind example is always running the same statement).

Here is an example of Binding to avoid this kind of injection:

import java.sql.*;

public class Bind {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=postgres";
        Connection conn = DriverManager.getConnection(url);

        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?";
        System.out.println(sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, args[0]);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

Running this with the same input as the previous example shows the malicious code does not work because there is no paymentType matching that string:

c:temp>java Bind salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?
salary 50

c:temp>java Bind "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?

Basically, with prepared statements the data coming in from a potential hacker is treated as data - and there's no way it can be intermixed with your application SQL and/or be interpreted as SQL (which can happen when data passed in is placed directly into your application SQL).

This is because prepared statements "prepare" the SQL query first to find an efficient query plan, and send the actual values that presumably come in from a form later - at that time the query is actually executed.

More great info here:

Prepared statements and SQL Injection

链接地址: http://www.djcxy.com/p/16768.html

上一篇: 这个SQL注入是如何工作的？需要说明