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

Answer 1

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.

Answer 2

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.

Answer 3

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.