I'm trying to insert values in the contents table. It works fine if I do not have a PHP variable inside VALUES. When I put the variable $type
inside VALUES
then this doesn't work. What am I doing wrong?
$type = 'testing'; mysql_query("INSERT INTO contents (type, reporter, description) VALUES($type, 'john', 'whatever')");
$type = 'testing'; mysql_query("INSERT INTO contents (type, reporter, description) VALUES($type, 'john', 'whatever')");
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.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
A variable starts with the $ sign, followed by the name of the variable. A variable name must start with a letter or the underscore character. A variable name cannot start with a number. A variable name can only contain alpha-numeric characters and underscores (A-z, 0-9, and _ )
The rules of adding a PHP variable inside of any MySQL statement are plain and simple:
So as your example only involves data literals, then all variables must be added through placeholders (also called parameters). To do so:
And here is how to do it with all popular PHP database drivers:
Such a driver doesn't exist.
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 (...)
$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.
Sometimes 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";
To avoid SQL injection the insert statement with be
$type = 'testing'; $name = 'john'; $description = 'whatever'; $con = new mysqli($user, $pass, $db); $stmt = $con->prepare("INSERT INTO contents (type, reporter, description) VALUES (?, ?, ?)"); $stmt->bind_param("sss", $type , $name, $description); $stmt->execute();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With