When *Not* to Use Prepared Statements

When *not* to use prepared statements?

I think you want PDO::ATTR_EMULATE_PREPARES. That turns off native database prepared statements, but still allows query bindings to prevent sql injection and keep your sql tidy. From what I understand, PDO::MYSQL_ATTR_DIRECT_QUERY turns off query bindings completely.

Should you use prepared statements for their escaping only?

The difference considered to be negligible.

Nevertheless, one have to distinguish native prepared statements from the general idea of a prepared statement.

The former is just a form of running queries supported by most of DBMS, explained here. Its usage can be questioned.

The latter is a general idea of substituting actual data with a placeholder, implying further processing of the substituted data. It is widely used in programming, a well-known printf() function is an example. And this latter approach have to be ALWAYS used to run a query against a database, no matter if it is backed by native prepared statements or not. Because:

• prepared statement makes proper formatting (or handling) inevitable.
• prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
  
  
  • some PHP 'magic' feature which rather spoils the data than make it safe.
  • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
• prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
• prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

So, even if you consider not using native prepared statements (which is quite okay), you have to always create your queries using placeholders instead of the actual data. For this purpose you can use PDO, which works exactly as described above - by default it just emulate prepares, means regular SQL query being created out prepared query and data, and then run against database.

However, PDO lacks support for many important data types, such as identifier or an array - thus it makes you unable to always use placeholders and thus makes an injection quite possible. Luckily, safeMysql has placeholders for the every data type and allows you to run queries safely.

When should I use prepared statements?

tl/dr

Always. 100% of the time, use it. Always; and even if you don't need to use it. USE IT STILL.

---

mysql_* functions are deprecated. (^{Notice the big red box?})

Warning This extension was deprecated in PHP 5.5.0, and it was removed
in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be
used. See also MySQL: choosing an API guide and related FAQ for more
information. Alternatives to this function include:

• mysqli_connect()
• PDO::__construct()

You'd be better off using PDO or MySQLi. Either of those 2 will suffice as compatible libraries when using prepared statements.

Trusting user input without prepared statements/sanitizing it is like leaving your car in a bad neighborhood, unlocked and with the keys in the ignition. You're basically saying, just come on in and take my goodies

You should never, and I mean never, trust user input. Unless you want this:

In reference to the data and storing it, as stated in the comments, you can never and should never trust any user related input. Unless you are 101% sure the data being used to manipulate said databases/values is hard-coded into your app, you must use prepared statements.

Now onto why you should use prepared statements. It's simple. To prevent SQL Injection, but in the most straight forward way possible. The way prepared statements work is simple, it sends the query and the data together, but seperate (if that makes sense haha) - What I mean is this:

Prepared Statements
Query: SELECT foo FROM bar WHERE foo = ?
Data:  [? = 'a value here']

Compared to its predecessor, where you truncated a query with the data, sending it as a whole - in turn, meaning it was executed as a single transaction - causing SQL Injection vulnerabilities.

And here is a pseudo PHP PDO example to show you the simplicity of prepared statements/binds.

$dbh = PDO(....); // dsn in there mmm yeahh
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

^{Taken from PHP Manual for PDO Prepared Statements}

---

More Reading

• How can I prevent SQL-injection in php?
• What is SQL-injection? (^{Simple Terms})

Should we always use prepared statements in MySQL and php or when to use these?

Non-prepared statements are sufficient if you have an SQL query that is entirely hard-coded, and needs no PHP variables in the SQL.

Here's an example:

$result = $mysqli->query("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL 7 DAY");

The query is self-contained. It's just a fixed string, entirely under control of your application. There's no way any untrusted content can affect the query.

If your query needs some variable part, then use query parameters, like this:

$stmt = $mysqli->prepare("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL ? DAY");
$stmt->bind_param("i", $number_of_days);
$stmt->execute();

The point of query parameters is to separate potentially untrusted content from the SQL parsing step. By using parameters, the value of the bound variable is not combined with the query until after the SQL has been parsed. Therefore there is no way the bound parameter can affect the logic of the query — the parameter will be limited to act as a single scalar value in the query.

Is it necessary to use a prepared statement for a select statement without user input?

Necessary, no. Recommended, yes. However the way your query is currently written you get no benefit from the prepared statement. Instead it should be:

mysqli_stmt_prepare($stmt, 
    'SELECT client_account_status FROM version_control WHERE id = ?'));

mysqli_stmt_bind_param($stmt, 'i', 1);
mysqli_stmt_execute($stmt);

The problem with your initial version is that mysql has no way of knowing what part of your query is a parameter and what part is the SQL statement. The point of parameterized queries is to clearly separate the statement from the parameters.

See How can I prevent SQL injection in PHP? for more information on preventing SQL injection.

Is it safe NOT to use prepared-statement after I have used it once?

If the username has a special character in it, it will affect your second query. This is referred to as a second order SQL injection.

Are there downsides to using prepared statements?

Prepared statement is just a parsed and precompiled SQL statement which just waits for the bound variables to be provided to be executed.

Any executed statement becomes prepared sooner or later (it need to be parsed, optimized, compiled and then executed).

A prepared statement just reuses the results of parsing, optimization and compilation.

Usually database systems use some kind of optimization to save some time on query preparation even if you don't use prepared queries yourself.

Oracle, for instance, when parsing a query first checks the library cache, and if the same statement had already been parsed, it uses the cached execution plan instead.

prepared statements - are they necessary

Prepared statements offer excellent protection against SQL injection.

In addition to SQL injection protection, prepared statements offer reduced load on the database server when the same query is to executed multiple times, such as in an INSERT loop. The statement is only compiled once by the RDBMS rather than needing to be compiled each time as it would in a mysql_query() call.

Different APIs require varying amounts of code to execute a prepared statement. I find that PDO can be a little less verbose than MySQLi, if for example your situation permits the use of implicit parameter binding inside the execute() call. This only works, if all your params can be evaluated as strings though.

// PDO implicit binding example:
// Not many lines of code if the situation allows for it
$stmt = $pdo->prepare("SELECT * FROM tbl WHERE col1=? AND col2=? AND col3=?");
$stmt->execute(array($val1, $val2, $val3));

---

Related Topics