Edit: Discussion split from How did my DB get hacked?
Sorry, but if you switch to prepared statements you should use named statements. Using this ? Statements is completely senseless
Why is that?
1 Like
Citation please. For the record, I completely disagree. Please state your case for “senseless”.
They are actually called named place-holders, and they are a waste of typing and processing time. They only exist within the PDO driver, where they are converted to positional ? place-holders before being sent to the database server, both in the sql query statement and in the execute data communication.
1 Like
Citation please? I smell micro optimization.
No smell test is needed. Regardless of updating old (what the OP is doing) or writing new code/query(ies) you don’t get paid for making up and typing intermediate names two times, adding :, quotes, and => where needed, then correcting any typo mistakes.
As to does this get replaced within the driver by positional place-holders, do a web search on - MySql prepared query binary transfer protocol, to find out exactly what is communicated between php and the database server for prepared queries.
Personally I like the named placeholders. If I understand correctly, with ? you have to supply the data in the order of the ? in the statement, whereas with the placeholders, you can supply the data in any order and it will be plugged into the proper position.
True, but when converting 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 order. You don’t need to make more work for yourself, which you are already complaining about. Practice Keep It Simple (KISS) and Don’t Repeat Yourself (DRY) programming.
Did you ever used INSERT INTO … ON DUPLICATE KEY UPDATE…
With more then 3 columns in a table?
I use it very often to even add a new record or update an old one because it saves me testing if an row already exists. And with ? You have to insert all columns two times into the query.
Also it is very simple 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 (MySQL 8.0.19) row alias 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 are using emulated prepared queries. This is not recommend, even by php, since if you don’t set the character set to match your database table(s) 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 fool-proof way of preventing sql injection for all data types is to use a true prepared query.
I sense several problems with whatever it is you are doing. Please expound on what your doing along with code or a repo link.
I find named placeholders very valuable and can save a person 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] . "<br>";
*
* 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;
}
<form id="formData" class="checkStyle" action="create_procedure.php" method="post" enctype="multipart/form-data">
<input type="hidden" name="cms[user_id]" value="3">
<input type="hidden" name="cms[author]" value="<?= Login::full_name() ?>">
<input type="hidden" name="action" value="upload">
<div class="file-style">
<input id="file" class="file-input-style" type="file" name="image">
<label for="file">Select file</label>
</div>
<select class="select-css" name="cms[page]">
<option value="index" selected>Home</option>
<option value="blog">Blog</option>
<option value="about">About</option>
</select>
<div class="heading-style">
<label class="heading_label_style" for="heading">Heading</label>
<input class="enter_input_style" id="heading" type="text" name="cms[heading]" value="" tabindex="1" required
autofocus>
</div>
<div class="content-style">
<label class="text_label_style" for="content">Content</label>
<textarea class="text_input_style" id="content" name="cms[content]" tabindex="2"></textarea>
</div>
<div class="submit-button">
<button class="form-button" type="submit" name="submit" value="enter">submit</button>
</div>
</form>
Hi @Pepster64,
I use a very similar function myself. In the case of using a function/method such as we use, “saving typing” is really moot when it comes to named parameters vs positional placeholders (?) since we are not typing anything anyways. The function could just as easily use positional placeholders. It makes no difference for the developer either way.
<OFFTOPIC>
As to your implementation, it can be cleaned up quite a bit. The function is doing too much. It should have a “Single Responsibility”, inserting data. The function not only inserts data but also does error handling. The 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 does an ID check. On an insert, there should not be an ID sent in the first place so you can remove that. There should be an error if an improper query is executed. That type of check should be in an update function/method.
The SQL also does not account for reserved words being used for column names (bad practice). Backticks should be added.
I see in your form you are making all the field names arrays. I tried that before (Novice To Ninja book technique). I didn’t find any benefit to doing that.
This is 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);
}
</OFFTOPIC>
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.