Problem Set 7 (fighting of SQL injection attack)

Question

Halfway the PS-text it says that using the "query'-function will also ensure "that any such placeholders' values are properly escaped so that your code is not vulnerable to SQL injection attacks.", but I don't see how that would solve an SQL injection. If I would type the SQL-code I want to inject in the Username-textbox it will still end up in the actual SQL-query.

Or is it the injected code is actually executed but the returned result will not be visible?

Answer 1

Not really. The function query uses another function from the standard library namely prepare that prevents SQL injection attacks by eliminating the need to manually quote the parameters as the documentation suggests.

The idea is fairly simple, but first, let's see what a prepared statement means.

As this article suggests,

Prepared statements, also known as parameterized statements or parameterized SQL, can be thought of as a template for SQL statements. Prepared statements allow database engines to run SQL statements more efficiently because the same (or similar) SQL is used over and over again. The key feature of a prepared statement is the fact that values can be plugged into the query after the query is “prepared”, and ready to be executed.

If you look at the code for query (I replaced the irrelevant parts with comments for the sake of shortness)

function query(/* $sql [, ... ] */)
{
    // SQL statement
    $sql = func_get_arg(0);

    // parameters, if any
    $parameters = array_slice(func_get_args(), 1);

    // try to connect to database [removed]

    // prepare SQL statement
    $statement = $handle->prepare($sql);
    // some code [removed]

    // execute SQL statement
    $results = $statement->execute($parameters);

    // return result set's rows, if any [removed]
}

You'll find 4 main parts here

1. storing the SQL statement into $sql
2. storing the parameters into $parameters
3. preparing the statement
4. passing the parameters and executing the statement

This denotes that the process of sending a statement to the server to prepare is separate from the process of executing this statement.

And that's exactly the idea behind prepared statements being effective against SQL injection attacks because injection attacks mainly depend on inserting the data inputted by the user directly into the SQL statement.

When using prepared statements, the data is sent to the server after the statement is sent. This means that the data is never interpreted as SQL code.