P粉9498488492023-08-24 16:07:56
To avoid SQL injection, insert statements 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();
P粉8832233282023-08-24 15:43:16
The rules for adding PHP variables in any MySQL statement are simple and straightforward:
This rule covers 99% of queries, specifically yours. Any variable representing an SQL data literal (or, simply - an SQL string or number) must be added via a prepared statement. Without exception.
This method involves four basic steps
Here's how to do it using all popular PHP database drivers:
mysqli
Add data textCurrent PHP versions allow you to prepare/bind/execute in a single call:
$type = 'testing'; $reporter = "John O'Hara"; $query = "INSERT INTO contents (type, reporter, description) VALUES(?, ?, 'whatever')"; $mysqli->execute_query($query, [$type, $reporter]);
If you have an older version of PHP, preparation/binding/execution must be done explicitly:
$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 complex, but a detailed explanation of all these operators can be found in my article, How to run an INSERT query using Mysqli , and a solution that significantly simplifies the process.
For SELECT queries, you can use the same method as above:
$reporter = "John O'Hara"; $result = $mysqli->execute_query("SELECT * FROM users WHERE name=?", [$reporter]); $row = $result->fetch_assoc(); // or while (...)
However, if you have an older version of PHP, you will need to perform a prepare/bind/execute routine and add a call to the get_result()
method to get the familiar < code>mysqli_result You can get data from it in 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 combine the bind and execute parts, which is very convenient. PDO also supports named placeholders, which some people find very convenient.
Any other query parts, such as SQL keywords, table or field names, or operators - must be filtered by whitelisting .
Sometimes we have to add a variable that represents another part of the query, such as a keyword or identifier (database, table or field name). This situation is rare, but it's best to be prepared.
In this case, your variable must be checked against the list of values explicitly written in the script. My other articleAdding field names in ORDER BY clause based on user selectionexplains this:
This 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"); }
$direction = $_GET['direction'] ?: "ASC"; $allowed = ["ASC","DESC"]; $key = array_search($direction, $allowed, true); if ($key === false) { throw new InvalidArgumentException("Invalid ORDER BY direction"); }
After code like this, both the $direction
and $orderby
variables can be safely put into the SQL query because they will either be equal to one of the allowed variants or will will throw an error.
The last thing to mention about identifiers is that they must also be formatted according to the specific database syntax. For MySQL, it should be backtick
characters around the identifier. Therefore, the final query string for our order example will be
$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";