How to include PHP variables in MySQL statements

Question

I'm trying to insert values ​​into a table of contents. If I don't have PHP variables in VALUES it works fine. This doesn't work when I put the variable $type into VALUES . What did i do wrong?

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description)
     VALUES($type, 'john', 'whatever')");

Answer 1

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();

Answer 2

The rules for adding PHP variables in any MySQL statement are simple and straightforward:

1. Use prepared statements

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

• In the SQL statement, replace all variables with placeholders
• PreparationResult Query
• Bind variables to placeholders
• ExecuteQuery

Here's how to do it using all popular PHP database drivers:

UsemysqliAdd data text

Current 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 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 (...)

Use PDO to add data text

$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.

2. Use whitelist filtering

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";