The rules of adding a PHP variable inside of any MySQL statement are plain and simple: - Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) MUST be added through a prepared statement. No exceptions.
- Any other query part, such as an SQL keyword, a table or a field name, or an operator - must be filtered through a white list.
So as your example only involves data
literals, then all variables must be added through placeholders (also called parameters). To do so: - In your SQL statement, replace all variables with placeholders
- prepare the resulting query
- bind variables to placeholders
- execute the query
And here is how to do it with all popular PHP database drivers: Adding data literals using mysql extSuch a driver
doesn't exist. Adding data literals using mysqli$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description)
VALUES(?, ?, 'whatever')";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $type, $reporter);
$stmt->execute();
The code is a bit complicated but the detailed explanation of all these operators can be found in my article, How to run an INSERT query using Mysqli, as well as a solution that eases the process dramatically. For a SELECT
query you will need to add just a call to get_result() method to get a familiar mysqli_result from which you can fetch the data the usual way: $reporter = "John O'Hara";
$stmt = $mysqli->prepare("SELECT * FROM users WHERE name=?");
$stmt->bind_param("s", $reporter);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc(); // or while (...)
Adding data literals using PDO$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description)
VALUES(?, ?, 'whatever')";
$stmt = $pdo->prepare($query);
$stmt->execute([$type, $reporter]);
In PDO, we can have the bind and execute parts combined, which is very convenient. PDO also supports named placeholders which some find extremely convenient. Adding keywords or identifiersSometimes we have to add a variable that represents another part of a query, such as a keyword or an
identifier (a database, table or a field name). It's a rare case but it's better to be prepared. In this case, your variable must be checked against a list of values explicitly written in your script. This is explained in my other article, Adding a field name in the ORDER BY clause based on the user's choice: Unfortunately, PDO has no placeholder for identifiers
(table and field names), therefore a developer must filter them out manually. Such a filter is often called a "white list" (where we only list allowed values) as opposed to a "black-list" where we list disallowed values. So we have to explicitly list all possible variants in the PHP code and then choose from them.
Here is an example: $orderby = $_GET['orderby'] ?: "name"; // set the default value
$allowed = ["name","price","qty"]; // the white list of allowed field names
$key = array_search($orderby, $allowed, true); // see if we have such a name
if ($key === false) {
throw new InvalidArgumentException("Invalid field name");
}
Exactly the same approach should be used for the direction,
$direction = $_GET['direction'] ?: "ASC";
$allowed = ["ASC","DESC"];
$key = array_search($direction, $allowed, true);
if ($key === false) {
throw new InvalidArgumentException("Invalid ORDER BY direction");
}
After such a code, both
$direction and $orderby variables can be safely put in the SQL query, as they are either equal to one of the allowed variants or there will be an error thrown. The last thing to mention about identifiers, they must be also formatted according to the particular database syntax. For MySQL it should be backtick characters around the identifier. So the final query string for our order by example would be $query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";
- Running INSERT query with raw Mysqli
- Explanation
- INSERT query with a helper function
- INSERT query from an array
- Comments (2)
It goes without saying that you must use prepared statements for any SQL query that would contain a PHP variable. Therefore, as usually the INSERT query makes little sense without variables, it should always run through a prepared statement. To do so: - create a correct SQL INSERT statement
- replace all variables in the query with with question
marks (called placeholders or parameters)
- prepare the resulting query
- bind variables to placeholders
- execute the query
Just make sure you've got a properly configured mysqli connection variable that is required in order to run SQL queries and to inform you of the possible errors. Running INSERT query with raw MysqliJust write a code like in this example
$sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)";
$stmt= $conn->prepare($sql);
$stmt->bind_param("sss", $name, $email, $password);
$stmt->execute();
and have your query executed without a single syntax error or SQL injection! ExplanationWhat is going on here? $sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)";
Like it was said above, first we are writing an SQL query where all variables are substituted with question marks. IMPORTANT! there should be no quotes around question marks, you are adding placeholders, not strings.
$stmt= $conn->prepare($sql);
Then, the query is prepared. The idea is very smart.
To avoid even a possibility of the SQL injection or a syntax error caused by the input data, the query and the data are sent to database server separately. So it goes on here: with prepare() we are sending the query to database server ahead. A special variable, a statement is created as a result. We would use this variable from now on. $stmt->bind_param("sss", $name, $email, $passwor);
Then variables must be bound to the statement. The call consists of two parts - the string with types and the list of variables.
With mysqli, you have to designate the type for each bound variable. It is represented by a single letter in the first parameter. The number of letters should be always equal to the number of variables. The possible types are - i for integer
- d fof double (float)
- s for string
- b for blobs
NOTE that you can almost always safely use "s" for any variable.
So you can tell now that "sss" means "there would be 3
variables, all of string type". And then, naturally, three variables obediently follow. $stmt->execute();
Then the query finally gets executed. Means variables get sent to database server and the query is actually executed. NOTE that you don't have to check the execution result. Given you have the proper connection code mentioned above, in case of error mysqli will raise an error automatically.
INSERT query with a helper function
As you may noted, the code is quite verbose. If you like to build a code like a Lego figure, with shining ranks of operators, you may keep it as is. If you, like me, hate useless repetitions and like to write concise and meaningful code, then there is a simple helper function. With it, the code will become two times shorter: $sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)";
prepared_query($conn, $sql, [$name, $email, $password]);
Here we are calling the helper function using a connection variable, an sql query and an
array with variables. Simple, clean and safe! INSERT query from an arrayIt is often happens that we have an array consists of fields and their values that represents a row to be inserted into a database. And naturally it would be a good idea to have a function to convert such an array into a correct SQL INSERT statement and execute it. So here it goes (utilizing a helper function mentioned above but you can easily rewrite it to raw mysqli if you'd like to): First
of all this function will need a helper function of its own. We will need a function to escape MySQL identifiers. Yes, all identifiers must be quoted and escaped, according to MySQL standards, in order to avoid various syntax issues. function escape_mysql_identifier($field){
return "`".str_replace("`", "``", $field)."`";
}
And now we can finally have a function that accepts a table name and an array with data and runs a prepared INSERT query against a database: function prepared_insert($conn, $table, $data) {
$keys = array_keys($data);
$keys = array_map('escape_mysql_identifier', $keys);
$fields = implode(",", $keys);
$table = escape_mysql_identifier($table);
$placeholders = str_repeat('?,', count($keys) - 1) . '?';
$sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
prepared_query($conn, $sql, array_values($data));
}
Related articles:- Mysqli SELECT query with prepared statements
- How to run a SELECT query using Mysqli
- How to run an UPDATE query using Mysqli
- Mysqli
prepared statement with multiple values for IN clause
- Using mysqli prepared statements with LIKE operator in SQL
- Mysqli examples
- How to call stored procedures with mysqli
- How to create a search filter for mysqli
- How to run 1000s insert queries with mysqli?
| 
