Can Parameterized Statement Stop All SQL Injection

Can parameterized statement stop all SQL injection?

The links that I have posted in my comments to the question explain the problem very well. I've summarised my feelings on why the problem persists, below:

1. Those just starting out may have no awareness of SQL injection.

2. Some are aware of SQL injection, but think that escaping is the (only?) solution. If you do a quick Google search for php mysql query, the first page that appears is the mysql_query page, on which there is an example that shows interpolating escaped user input into a query. There's no mention (at least not that I can see) of using prepared statements instead. As others have said, there are so many tutorials out there that use parameter interpolation, that it's not really surprising how often it is still used.

3. A lack of understanding of how parameterized statements work. Some think that it is just a fancy means of escaping values.

4. Others are aware of parameterized statements, but don't use them because they have heard that they are too slow. I suspect that many people have heard how incredibly slow paramterized statements are, but have not actually done any testing of their own. As Bill Karwin pointed out in his talk, the difference in performance should rarely be used as a factor when considering the use of prepared statements. The benefits of prepare once, execute many, often appear to be forgotten, as do the improvements in security and code maintainability.

5. Some use parameterized statements everywhere, but with interpolation of unchecked values such as table and columns names, keywords and conditional operators. Dynamic searches, such as those that allow users to specify a number of different search fields, comparison conditions and sort order, are prime examples of this.

6. False sense of security when using an ORM. ORMs still allow interpolation of SQL statement parts - see 5.

7. Programming is a big and complex subject, database management is a big and complex subject, security is a big and complex subject. Developing a secure database application is not easy - even experienced developers can get caught out.

8. Many of the answers on stackoverflow don't help. When people write questions that use dynamic SQL and parameter interpolation, there is often a lack of responses that suggest using parameterized statements instead. On a few occasions, I've had people rebut my suggestion to use prepared statements - usually because of the perceived unacceptable performance overhead. I seriously doubt that those asking most of these questions are in a position where the extra few milliseconds taken to prepare a parameterized statement will have a catastrophic effect on their application.

How do parameterized queries help against SQL injection?

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query. That is, if the input contains SQL, it can't become part of what is executed because the SQL is never injected into the resulting statement.

How does SQLParameter prevent SQL Injection?

Basically, when you perform a SQLCommand using SQLParameters, the parameters are never inserted directly into the statement. Instead, a system stored procedure called sp_executesql is called and given the SQL string and the array of parameters.

When used as such, the parameters are isolated and treated as data, instead of having to be parsed out of the statement (and thus possibly changing it), so what the parameters contain can never be "executed". You'll just get a big fat error that the parameter value is invalid in some way.

How does using parameters prevent SQL injection?

Because simply entering "drop database" into the field on its own is not sufficient. You'd have to add some other characters to convince the SQL interpreter to terminate the previous statement and start a new one (to execute the DROP). Parameterisation will, among other things, prevent those kind of sequences from successfully being injected and interpreted in the intended manner by SQL. Characters such as ' for instance, which might close a string, will be escaped so that they are considered part of a variable, not part of the SQL.

Specifically, http://bobby-tables.com/about contains a worked example. In the example, there is some PHP code to insert a row into a table:

$sql = "INSERT INTO Students (Name) VALUES ('" . $studentName . "');";
execute_sql($sql);

If $studentName is set to something normal like "John", then the final SQL string produced by the code is benign:

INSERT INTO Students (Name) VALUES ('John');

Equally, similar to your example, if $studentName was set to "DROP TABLE Students", it still wouldn't have any effect. The final SQL would be:

INSERT INTO Students (Name) VALUES ('DROP TABLE Students');

No harm done.

But...if $studentName is set to something a bit more subtle, like this:

Robert'); DROP TABLE Students;--

The final string looks like this:

INSERT INTO Students (Name) VALUES ('Robert'); DROP TABLE Students;--');

This is then passed to the SQL engine, which interprets it as two statements (with, incidentally, a comment at the end) and executes them both, with unpleasant consequences.

However, if the value in $studentName had been passed as a parameter instead of just joined to a standard string, the final query would have ended up as

INSERT INTO Students (Name) VALUES ('Robert\'); DROP TABLE Students;--'); 

Notice the escaped ' in the middle, so it's now considered part of the string. It no longer causes the string to stop after "t".

Therefore what gets entered into the Name field in the table will be

Robert'); DROP TABLE Students;--

The DROP TABLE statement won't get executed because the SQL interpreter never sees it - it just treats it as part of the value to be added to the table.

Parameterisation means that anything within the parameter value is treated as a string (or possibly number) only - it can never escape outside that and be considered as part of the SQL statement itself.

Further reading:

http://bobby-tables.com

How does the SQL injection from the "Bobby Tables" XKCD comic work?

might help you understand better.

How does a PreparedStatement avoid or prevent SQL injection?

The problem with SQL injection is, that a user input is used as part of the SQL statement. By using prepared statements you can force the user input to be handled as the content of a parameter (and not as a part of the SQL command).

But if you don't use the user input as a parameter for your prepared statement but instead build your SQL command by joining strings together, you are still vulnerable to SQL injections even when using prepared statements.

How can I prevent SQL injection in PHP?

The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create an SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

1. Using PDO (for any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // Do something with $row
   }
   

2. Using MySQLi (for MySQL):

   $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
   $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
   $stmt->execute();
   
   $result = $stmt->get_result();
   while ($row = $result->fetch_assoc()) {
       // Do something with $row
   }
   

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

---

Correctly setting up the connection

PDO

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example, the error mode isn't strictly necessary, but it is advised to add it. This way PDO will inform you of all MySQL errors by means of throwing the PDOException.

What is mandatory, however, is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.

Mysqli

For mysqli we have to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // charset

---

Explanation

The SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend.

Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);

---

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

Preventing SQL Injection in PHP without parameterized queries?

• check every single user input for datatype and where applicabile with regular expressions (golden rule is: never EVER trust user input)
• use prepared statements
• seriously: prepared statements :)

it's a lot of work especially if your application is in bad shape (like it seems to be in your case) but it's the best way to have a decent security level

the other way (which i'm advising against) could be virtual patching using mod_security or a WAF to filter out injection attempts but first and foremost: try to write robust applications
(virtual patching might seem to be a lazy way to fix things but takes actually a lot of work and testing too and should really only be used on top of an already strong application code)

How parameterized queries/prepared statements better in SQL injection than escaping user input

Q: Can you give an example that parameterized query prevent the SQL injection attack when a user input to the query still contains a special character to cause harm?

A: There have been some multibyte character exploits in code that doesn't properly account for character sets, resulting in holes in the escaping mechanism. (Where the "escape string" thinks it's working on a string in particular encoding, but the actual bytes are in a different encoding, and sneakily sliding single quotes into the SQL text.)

But I don't really think that's the strongest argument for prepared statements with bind placeholders.

A strong argument is that when we look at the code, we see static SQL text, not dynamically generated...

 $sql = 'SELECT fee, fi FROM fo WHERE fum = ?'; 
 $dbh->prepare($sql);

We see that code, and we look at that SQL text... and we recognize immediately, there is no way that the SQL text is going to be other than what we see. We don't have to look anywhere else in the code; we see it right there on two lines.

When we see this:

 $sql = "SELECT fee, fi FROM fo WHERE fum = $fumval";

That's double quotes, there's variable interpretation going on. Is $fumval guaranteed to be safe for inclusion in the SQL text, where did $fumval come from? Should there be single quotes around $fumval, or are we guaranteed that it's already enclosed in single quotes?

Okay, maybe there's a line right before that:

 $fumval = $dbh->quote($unsafe_fumval);

If that line isn't right above the generation of the SQL text, we need to go check... are we guaranteed the $fumval is safe?

The point is this... the SQL is being dynamically constructed. It might be better if it was done like this:

$sql = 'SELECT fee, fi FROM fo WHERE fum = ' . $dbh->quote($unsafe_fumval);

For a simple statement, maybe it's six of one and half a dozen of the other. But when SQL statements get larger, involving multiple tables and dozens of column references and values, the dynamic construction gets harder to verify that there are not any problems in it.

Is it possible to write secure code using dynamically generated SQL and "escape string" processing on values? YES.

Is it possible to write vulnerable code that uses prepared statements with dynamically generated SQL text? YES.

---

It's really the pattern of static SQL text, passing values provided through bind placeholders is what gets us the bang for our buck... code that is written in a way in which we can identify as not vulnerable to SQL injection.

Prevent this statement from SQL injection

That query cannot fall into SQL injection. The queries that fall in this category are those queries that you build by plain String concatenation. For example:

String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colX = " + stringParameter;
Statement stmt = con.createStatement(query);
ResultSet rs = stmt.executeQuery();

In your case, there's no parameter to inject, so there's no way to have a SQL injection attack for your specific case.

If you need to prevent from SQL injection attacks, use PreparedStatement and do not concatenate the query. Instead, pass the parameters through the interface, which will escape any invalid character for you:

String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colX = ?";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, stringParameter);
ResultSet rs = pstmt.executeQuery();

In case you need to build a dynamic query, then you may fall back into concatenating strings, regardless if you use plain String concatenation or a StringBuilder:

//Common solution, still suffers from SQL injection
String query = "SELECT COUNT(*) FROM EZ_DAY WHERE 1 = 1 ";
if (stringParameter != null) {
    query = query + = "AND colX = " + stringParameter;
}

Instead, it is better to use a COALESCE or IFNULL function to the parameter to avoid such situations:

//Better solution
String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colx = COALESCE(?, colx)";

In the case above:

• If the parameter has a different value than null, the query would be like this:

  String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colx = ?";
  

• If the parameter has null value, then the query would be like this:

  String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colx = colx";
  

In the last example, you're still able to use PreparedStatement and avoid SQL injection attacks.

Related:

• Difference between Statement and PreparedStatement

---

Related Topics