How to Include a PHP Variable Inside a MySQL Statement

How to include a PHP variable inside a MySQL statement

The rules of adding a PHP variable inside of any MySQL statement are plain and simple:

1. 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.
2. 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 ext

Such 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 identifiers

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

How to include a PHP variable inside a MySQL SELECT statement

Try to change quotes of your query to double quotes, as PHP will not substitute strings with variables if query is single-qouted. Also, inside your query you need to change string literals to single quotes, so it complies with SQL syntax.

Something like that:

$r = DBi::$conn->query(" -- <<< note double quote
        SELECT 
            a.pKey,
            a.Name,
            a.`Pic-Name`,
            a.GTIN,
            a.Type,
            a.Avail,
            (SELECT $varPriceCol FROM preise WHERE Art_pKey = a.pKey ORDER BY From_date DESC LIMIT 1) Price,
            (AVG((b.Preice / b.Art_Num) * -1.00)) Mid_price,
            a.Created
      FROM art a LEFT
        JOIN kasse b ON a.pKey = b.Art_pKey
     WHERE Aktiv = 'Y' AND Avail = 'Y' AND Visible = 'Y' -- <<< note single quotes
     GROUP BY a.pKey
     ORDER BY Avail DESC, Name ASC
    ") or trigger_error('Query Failed! SQL: ' . $r . ' - Error: ' . mysqli_error(DBi::$conn), E_USER_ERROR);

How to include a PHP variable inside a MySQL insert

You would prefer prepared statement, safer and cleaner.

 <?php
    $stmt = $dbh->prepare("INSERT INTO user(nom, prenom, Gsm, Email, Sexe, address) VALUES(:nom, :prenom, :mobile, :Nemail, :sexe, :address)");
    $stmt->bindParam(':nom', $_GET['nom'];
    $stmt->bindParam(':prenom', $_GET['prenom'];
    $stmt->bindParam(':mobile', $_GET['mobile'];
    $stmt->bindParam(':Nemail', $_GET['Nemail'];
    $stmt->bindParam(':sexe', $_GET['sexe'];
    $stmt->bindParam(':address', $_GET['address'];
    $stmt->execute();
    ?>

Using php variables inside MySQL insert statement

Just change the last one:

mysql_query("INSERT INTO subscribers (email, referral_id, user_id, ip_address)
VALUES ('$user_email', '$user_refer', '$user_share', '".$_SERVER['REMOTE_ADDR']."')");

How to pass a php variable in WHERE clause of SELECT statement?

You can either break the string and concatenate the variable with ".."

require("conn.php");
$module = $_POST[ 'postmodule' ];
$query = "SELECT width FROM modules WHERE code = '".$module."'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
echo $row['width'];

Or you could wrap the variable in {} within the string, as long as the string is wrapped in double quotes.

require("conn.php");
$module = $_POST[ 'postmodule' ];
$query = "SELECT width FROM modules WHERE code = '{$module}'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
echo $row['width'];

Also, if $module is a string you will need to wrap the variable in single quotes as I did above.

---

Related Topics