The Value of Named Placeholders in Prepared Statements – PHP – SitePoint Forums

Edit: Separate discussion of How was my database hacked?

Sorry, but if you switch to prepared statements, you must use named statements. Use this? The statements are completely nonsense


Quote please. For the record, I completely disagree. Please state your case for “insane”.

They are actually called named placeholders and are a waste of typing and processing time. They only exist in the PDO driver, where they are converted to ? placeholders before being sent to the database server, both in the sql query statement and in the runtime data communication.



1 like

Quote please? I smell the micro-optimization.

No smell test is necessary. Regardless of updating the old one (which the OP does) or writing new code/queries, you don’t get paid for creating and typing intermediate names twice, adding :, quotes and => if necessary, then correcting any typos.

As for replacing it in the driver with positional placeholders, do a web search for MySql Prepared Query Binary Transfer Protocol, to find out exactly what is communicated between php and the database server for prepared statements.

Personally, I like named placeholders. If I understood correctly, with you must provide the data in the order of ? in the declaration, whereas with placeholders you can provide the data in any order and it will be inserted in the correct position.

True, but when converting the old code, the variables you just removed from the sql query statement and are going to supply as an array to the ->execute() call were already in the order. You don’t need to do more work for yourself, which you are already complaining about. Practice Keep It Simple (KISS) and Don’t Repeat Yourself (DRY) programming.

Have you ever used INSERT INTO… ON DUPLICATE KEY UPDATE…

With more than 3 columns in a table?

I use it very often even to add a new record or update an old one because it saves me from testing if a row already exists. And with ? You must insert all columns twice in the query.

Also, it’s very easy to swap two columns if you don’t name them.

No, you don’t. You would use either the old VALUES() function (not the VALUE/VALUES keyword) or the new row alias (MySQL 8.0.19) to reference the single instance of the values ​​in the query – https://dev. mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Also, for your use case to work, you use emulated prepared statements. This is not recommended, even by php, because if you don’t set the charset to match your database tables when you make the connection, sql special characters in a value can still break the sql query syntax and be used to inject sql. The only surefire way to prevent SQL injection for all data types is to use a true prepared statement.

I sense several problems with everything you do. Please explain what you do with code or repository link.

I find named placeholders very valuable and can save a lot of typing:

For example:

/*
 * As long as you have the correct field names as the key and
 * the correct values in the corresponding keys the following
 * procedural function should work with no problem.
 *
 */


function insertData(array $data, $pdo, $table) {
    try {
        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Set up the query using prepared states with the values of the array matching
         * the corresponding keys in the array
         * and the array keys being the prepared named placeholders.
         */
        $sql="INSERT INTO " . $table . ' (' . implode(", ", array_keys($data)) . ')';
        $sql .= ' VALUES ( :' . implode(', :', array_keys($data)) . ')';

        /*
         * Prepare the Database Table:
         */
        $stmt = $pdo->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach ($data as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:

    } catch (PDOException $e) {

        /*
         * echo "unique index" . $e->errorInfo[1] . "
"; * * An error has occurred if the error number is for something that * this code is designed to handle, i.e. a duplicate index, handle it * by telling the user what was wrong with the data they submitted * failure due to a specific error number that can be recovered * from by the visitor submitting a different value * * return false; * * else the error is for something else, either due to a * programming mistake or not validating input data properly, * that the visitor cannot do anything about or needs to know about * * throw $e; * * re-throw the exception and let the next higher exception * handler, php in this case, catch and handle it */ if ($e->errorInfo[1] === 1062) { return false; } throw $e; } catch (Exception $e) { echo 'Caught exception: ', $e->getMessage(), "n"; // Not for a production server: } return true; }

Hi @Pepster64,

I use a very similar function myself. In the case of using a function/method like the one we’re using, “saving typing” is really useless when dealing with named parameters vs. positional (?) placeholders since we only type nothing anyway. The function could just as well use positional placeholders. It makes no difference to the developer anyway.


As for your implementation, it can be cleaned up a bit. The function does too much. It should have a “single responsibility”, inserting data. The function not only inserts data, but also handles errors. Error handling is easily handled elsewhere to handle ALL exceptions rather than a specific use case, therefore the try/catch should be removed.

The function also performs identity verification. On an insert, there shouldn’t be an id sent in the first place so you can delete it. There should be an error if an incorrect query is executed. This type of control must be in an update function/method.

The SQL also ignores reserved words used for column names (bad practice). Backticks must be added.

I see in your form that you create all arrays of field names. I’ve tried this before (technique from Novice To Ninja book). I haven’t found any advantage in doing so.

That’s all the function needs for named parameters…

    final public function insert(string $table, array $parameters): bool
    {
        $keys = implode('`, `', array_keys($parameters));
        $values = implode(', :', array_keys($parameters));

        $statement = $this->pdo->prepare("INSERT INTO $table (`$keys`) VALUES (:$values)");
        return $statement->execute($parameters);
    }

Harry L. Blanchard