When Should We Use a Preparedstatement Instead of a Statement

When is it better to use a Statement over a PreparedStatement?

In most real-life case, there is rarely case that createStatement is better than preparedStatement.

I believe almost everyone are aware of benefit of using prepared statement, just to name a few:

• Less vulnerable to sql injection
• better performance as to avoid SQL parsing same SQL with different parameter
• etc

Most of these benefits comes from reusing the same statement, and setting parameters separately (instead of embedding in the query itself) when you are using prepared statement.

The only benefit (that I am aware of) by using createStatement() is you can use same statement object to execute different SQLs, while when using prepared statement, you will need to create PrepredStatement for each query.

In real life this is seldom meaningful. However, when you are developing an application that will allow user to input arbitrary query (which means you cannot set parameter separately), then there is no obvious benefit of using prepared statement. And then, if such application is going to query a lot of times using different query string (for example, you are building a SQL client), then doing createStatement once, and reuse the Statement object to execute different queries may gain you some (marginal) performance gain with less object allocation.

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})

when to use Statement over Prepared Statement?

I suppose statement is used in queries with no parameter but why not use prepared statement ?

That's not even close. PreparedStatements are used in the case of INSERT, UPDATE and DELETE statements that return a ResultSet or an update count. They will not work for DDL statements as pointed out by Joachim, and neither will they work for invocation of stored procedures where a CallableStatement ought to be used (this is not a difference between the two classes). As far as queries with no bind parameters are concerned, PreparedStatements can turn out to be better than Statements (see below).

Which one is faster for queries with no params.

PreparedStatements will turn out to be faster in the long run, over extended use in a single connection. This is because, although PreparedStatements have to be compiled, which would take some time (this really isn't a lot, so don't see this as a drawback), the compiled version essentially holds a reference to the SQL execution plan in the database. Once compiled, the PreparedStatement is stored in a connection specific cache, so that the compiled version may be reused to achieve performance gains. If you are using JDBC Batch operations, using PreparedStatements will make the execution of the batch much faster than the use of plain Statement objects, where the plan may have to be prepared time and again, if the database has to do so.

which is faster? Statement or PreparedStatement

Prepared statements are much faster when you have to run the same statement multiple times, with different data. Thats because SQL will validate the query only once, whereas if you just use a statement it will validate the query each time.

The other benefit of using PreparedStatements is to avoid causing a SQL injection vulnerability - though in your case your query is so simple you haven't encountered that.

For your query, the difference between running a prepared statement vs a statement is probably negligible.

EDIT: In response to your comment below, you will need to look closely at the DAO class to see what it is doing. If for example, each time the method is called it re-creates the prepared statement then you will lose any benefit of using prepared statements.

What you want to achieve, is the encapsulation of your persistence layer so that their is no specific call to MySQL or Postgres or whatever you are using, and at the same time take advantage of the performance and security benefits of things like prepared statements. To do this you need to rely on Java's own objects such as PreparedStatement,.

I personally would build my own DAO class for doing CRUD operations, using Hibernate underneath and the Java Persistence API to encapsulate it all, and that should use prepared statements for the security benefits. If you have a specific use-case for doing repeated operations, then I would be inclined to wrap that within its own object.

Hibernate can be configured to use whatever database vendor you are using via an XML file, and thus it provides really neat encapsulation of your persistence layer. However, it is quite a complicated product to get right!

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.

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.

Should I use Prepared Statement for inserting one row?

When you use PreparedStement, not just your query execution is faster there are other advantages too.

• You queries execute fast as PreparedStatement results into the query being precompiled on the database and reused.
• PreparedStatement, your queries are dynamic. Meaning, you define the query only once, and reuse the same again with different parameters. String concatenation also achieves it but its crude way doing this. Quoting this link

The important thing to remember is to never construct SQL statements
using string concatenation of unchecked input values. Creating of
dynamic queries via the java.sql.Statement class leads to SQL
Injection.

• When you use PreparedStatement you prevent the SQL injection attacks. In Prepared statement, you do not use string concatenation for adding the runtime parameters but instead set the parameter explicitly in the compiled query and the parameters passed are escaped automatically by JDBC Driver for PreparedStatement.

---

Related Topics