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

Question

At the start of my application, I check for the account status. This is an internal check I control as I provide the application on a sub-domain.

I have been using this: // Retrieve account status for application $query = "SELECT `client_account_status` FROM `version_control` WHERE id = '1' ";

However, as I adapt to prepared statements, I am curious if this is absolutely necessary when there is no user input to check?

$stmt = mysqli_stmt_init($link);
if (mysqli_stmt_prepare($stmt, 'SELECT client_account_status FROM version_control WHERE id = 1')) {

    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $client_account_status);
    mysqli_stmt_fetch($stmt);
    mysqli_stmt_close($stmt);
}

No, but it's always a good idea to be consistent in your approach to SQL queries — Mark Baker, Mar 22 '16 at 22:16

score 4 · Accepted Answer · edited May 23 '17 at 11:45

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.

score 2 · Answer 2 · answered Mar 22 '16 at 22:15

No, it is not necessary when there is no user input. It can sometimes still be useful to use a prepared statement when there is input though, even if it's not user input. This is because preparing a statement allows it to be executed more efficiently if it is run lots of times with different data each time.

skrilled · Answer 3 · 2016-03-22T22:36:49.627

1

This question is going to have a lot of opinions, I'll add mine to the list...

I personally always go with PDO, or Doctrine's DBAL library.

A query using something like the DBAL class would happen as so:

$result = $db->fetchColumn('SELECT client_account_status FROM version_control WHERE id = :id', [
  'id' => $id
]);

In PDO it can be as easy as so:

$pdo->prepare('SELECT client_account_status FROM version_control WHERE id = :id');
$result = $pdo->execute(array(':id' => $id));

It's always easy to read and it doesn't require you to write 5 lines of code every single time you want to do something with the database.

You can have both security and efficiency, and it seems given how you asked your question that you only want to avoid mysqli prepared statements out of the complexity and annoyance of writing so much code for so little to happen.

edited Mar 22 '16 at 22:36

answered Mar 22 '16 at 22:35

skrilled

5,350
2
26
48

1

avoidance is probably true .. ha ha – Kirk Powell Mar 22 '16 at 22:36
I personally just hate the way mysqli handles parameter binding. Last thing I want to see when programming is `mysqli_stmt_bind_param($stmt, 'iisssi', ...` and a bunch of question marks in the query. I haven't used mysqli in years, I guarantee if you try something else you'll love it :p – skrilled Mar 22 '16 at 22:42
I prefer procedural over object-oriented, can PDO be used in conjunction with procedural code? (Yes, I am that inexperienced) – Kirk Powell Mar 22 '16 at 22:45

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

3 Answers3

Linked