Prepared Statements - Are They Necessary

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

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 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.

Are prepared statements necessary FOR ME?

You will be better off using prepared statement consistently.

Regular expressions are only a partial solution, but not as convenient or as versatile. If your variables don't fit a pattern that can be filtered with a regular expression, then you can't use them.

All the "ssisiisisis" stuff is an artifact of Mysqli, which IMHO is needlessly confusing.

I use PDO instead:

$sql = 'SELECT something FROM sometable WHERE pageid = ? LIMIT 1';
$stmt = $conn->prepare($sql);
$stmt->execute(array($pageid));

See? No need for regexp filtering. No need for quoting or breaking up the string with . between the concatenated parts.

It's easy in PDO to pass an array of variables, then you don't have to do tedious variable-binding code.

PDO also supports named parameters, which can be handy if you have an associative array of values:

$params = array("pageid"=>123, "user"=>"Bill");
$sql = 'SELECT something FROM sometable WHERE pageid = :pageid AND user = :user LIMIT 1';
$stmt = $conn->prepare($sql);
$stmt->execute($params);

If you enable PDO exceptions, you don't need to test whether the query succeeds. You'll know if it fails because the exception is thrown (FWIW, you can enable exceptions in Mysqli too).

You don't need to test for num_rows(), just put the fetching in a while loop. If there are no rows to fetch, then the loop stops immediately. If there's just one row, then it loops one iteration.

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    // do something with the database content

}

Prepared statements are easier and more flexible than filtering and string-concatenation, and in some cases they are faster than plain query() calls.

Is it necessary to close PreparedStatement before preparing another Statement

Strictly speaking, it is not necessary because you can have multiple prepared statements opened at the same time. What is necessary is to close every opened resource if it is not going to be utilised later.

Looking at your code, it doesn't ensure that the statement are, in fact, closed.

To ensure this, every close operation must be done inside a finally block: this way, it will executed whether the operation succeded or not.

Sample code:

PreparedStatement pstmt = null;
ResultSet r = null;
try {
    pstmt = conn.prepareStatement(firstsql);
    r = pstmt.executeQuery();
    // do something with the ResultSet
} finally {
    if (r != null) {
        try {
            r.close();
        } catch (SQLException e) {
            //log error
        }
    }
    if (pstmt != null) {
        try {
            pstmt.close();
        } catch (SQLException e) {
            //log error
        }
    }
}
//and do it again

This code can be greatly simplified if you are using Java 7 with the try-with-resources statement:

try (PreparedStatement pstmt = conn.prepareStatement(firstsql);
      ResultSet r = pstmt.executeQuery();) {
    // do something with the ResultSet
}

Should I always prefer working with prepared SQL statements, for performance benefits?

Prepared Statements:

Why use prepared statements?

There are numerous advantages to using
prepared statements in your
applications, both for security and
performance reasons.

Prepared statements can help increase
security by separating SQL logic from
the data being supplied. This
separation of logic and data can help
prevent a very common type of
vulnerability called an SQL injection
attack. Normally when you are dealing
with an ad hoc query, you need to be
very careful when handling the data
that you received from the user. This
entails using functions that escape
all of the necessary trouble
characters, such as the single quote,
double quote, and backslash
characters. This is unnecessary when
dealing with prepared statements. The
separation of the data allows MySQL to
automatically take into account these
characters and they do not need to be
escaped using any special function.

The increase in performance in
prepared statements can come from a
few different features. First is the
need to only parse the query a single
time. When you initially prepare the
statement, MySQL will parse the
statement to check the syntax and set
up the query to be run. Then if you
execute the query many times, it will
no longer have that overhead. This
pre-parsing can lead to a speed
increase if you need to run the same
query many times, such as when doing
many INSERT statements.

(Note: While it will not happen with
MySQL 4.1, future versions will also
cache the execution plan for prepared
statements, eliminating another bit of
overhead you currently pay for each
query execution.)

The second place where performance may
increase is through the use of the new
binary protocol that prepared
statements can use. The traditional
protocol in MySQL always converts
everything into strings before sending
them across the network. This means
that the client converts the data into
strings, which are often larger than
the original data, sends it over the
network (or other transport) to the
server, which finally decodes the
string into the correct datatype. The
binary protocol removes this
conversion overhead. All types are
sent in a native binary form, which
saves the conversion CPU usage, and
can also cut down on network usage.

When should you use prepared statements? Prepared statements can
be useful for all of the above
reasons, however they should not (and
can not) be used for everything in
your application. First off, the type
of queries that they work on is
limited to DML (INSERT, REPLACE,
UPDATE, and DELETE), CREATE TABLE, and
SELECT queries. Support for additional
query types will be added in further
versions, to make the prepared
statements API more general.

-> Sometimes prepared statements can actually be slower than regular
queries. The reason for this is that
there are two round-trips to the
server, which can slow down simple
queries that are only executed a
single time. In cases like that, one
has to decide if it is worth trading
off the performance impact of this
extra round-trip in order to gain the
security benefits of using prepared
statements.

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

Is mysql_real_escape_string() necessary when using prepared statements?

No, prepared queries (when used properly) will ensure data cannot change your SQL query and provide safe querying. You are using them properly, but you could make just one little change. Because you are using the '?' placeholder, it is easier to pass params through the execute method.

$sql->execute([$consulta]);

Just be careful if you're outputting that to your page, SQL parameter binding does not mean it will be safe for display within HTML, so run htmlspecialchars() on it as well when outputting.

---

Related Topics