Return to Answer

###Update

Update

Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to it dynamically: -

• a string
• a number
• an identifier
• a syntax keyword.

$orders  = array("name", "price", "qty"); // Field names
$key     = array_search($_GET['sort'], $orders)); // See if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. smart enuf :)
$query   = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe

However, there is another way to secure identifiers - escaping. As long as you have an identifier quoted, you can escape backticks inside by doubling them.

As a further step, we can borrow a truly brilliant idea of using some placeholder (a proxy to represent the actual value in the query) from the prepared statements and invent a placeholder of another type - an identifier placeholder.

So, to make the long story short: it's a placeholder, not prepared statement can be considered as a silver bullet.

So, a general recommendation may be phrased as As long as you are adding dynamic parts to the query using placeholders (and these placeholders properly processed of course), you can be sure that your query is safe.

###Update

So, unlike whatever "escaping", prepared statements is the measure that indeed protects from SQL injection (when applicable).

If you're still not convinced, here are a step-by-step explanation I wrote, The Hitchhiker's Guide to SQL Injection prevention, where I explained all these matters in detail and even compiled a section entirely dedicated to bad practices and their disclosure.

Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to SQL dynamically: -

• a string
• a number
• an identifier
• a syntax keyword

$orders  = array("name", "price", "qty"); // Field names
$key = array_search($_GET['sort'], $orders)); // if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. 
$query = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe

To ease the process I wrote a whitelist helper function that does all the job in one line:

$orderby = white_list($_GET['orderby'], "name", ["name","price","qty"], "Invalid field name");
$query  = "SELECT * FROM `table` ORDER BY `$orderby`"; // sound and safe

There is another way to secure identifiers - escaping but I rather stick to whitelisting as a more robust and explicit approach. Yet as long as you have an identifier quoted, you can escape the quote character to make it safe. For example, by default for mysql you have to double the quote character to escape it. For other other DBMS escaping rules would be different.

So, a general recommendation may be phrased as

• Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) must be added through a prepared statement. No Exceptions.
• Any other query part, such as an SQL keyword, a table or a field name, or an operator - must be filtered through a white list.

###Update

So, unlike whatever "escaping", prepared statements is the measure that indeed protects from SQL injection (when applicable).

Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to it dynamically:

And prepared statements covers only two of them.

Or there is a slightly better answer that suggests just another method of string formatting and even boasts it as the ultimate panacea. While of course, it is not. This method is no better than regular string formatting, yet it keeps all its drawbacks: it is applicable to strings only and, as any other manual formatting, it's essentially optional, non-obligatory measure, prone to human error of any sort.

If you're still not convinced, here is a step-by-step explanation I wrote, The Hitchhiker's Guide to SQL Injection prevention, where I explained all these matters in detail and even compiled a section entirely dedicated to bad practices and their disclosure.

Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to it dynamically: -

And prepared statements cover only two of them.

Or there is a slightly better answer that suggests just another method of string formatting and even boasts it as the ultimate panacea. While of course, it is not. This method is no better than regular string formatting, yet it keeps all its drawbacks: it is applicable to strings only and, like any other manual formatting, it's essentially optional, non-obligatory measure, prone to human error of any sort.

If you're still not convinced, here are a step-by-step explanation I wrote, The Hitchhiker's Guide to SQL Injection prevention, where I explained all these matters in detail and even compiled a section entirely dedicated to bad practices and their disclosure.