22: Quote SQL String Literals with MySQLi
Overview
Section titled “Overview”Quote a name containing an apostrophe and round-trip it through a SELECT. Use the new API when building a string literal for SQL generation where binding parameters is unavailable.
Prerequisites
Section titled “Prerequisites”- PHP 8.6 CLI. Confirm the version with
php --version. - The
mysqliextension and a reachable local MySQL or MariaDB server. SetMYSQL_USERfor an account with permission to connect. Optionally setMYSQL_HOST(default127.0.0.1),MYSQL_PORT(default3306), andMYSQL_PASSWORDfor that account. - Review the previous chapter if you’re following the series in order.
Allow about 10 minutes for the walkthrough and exercise.
What You’ll Build
Section titled “What You’ll Build”A runnable mysqli-quote-string.php example.
Step 1: Run the example (~5 min)
Section titled “Step 1: Run the example (~5 min)”Run the following from the root of the Code with PHP repository.
php code/php-8-6/22-mysqli-quote-string/mysqli-quote-string.php<?php
declare(strict_types=1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);$db = new mysqli( getenv('MYSQL_HOST') ?: '127.0.0.1', getenv('MYSQL_USER') ?: 'root', getenv('MYSQL_PASSWORD') ?: '', port: (int) (getenv('MYSQL_PORT') ?: '3306'),);try { $db->set_charset('utf8mb4'); $quoted = $db->quote_string("O'Reilly"); $result = $db->query("SELECT $quoted AS label"); echo $result->fetch_assoc()['label'], PHP_EOL;} finally { $db->close();}Expected Result
Section titled “Expected Result”O'ReillyWhy It Works
Section titled “Why It Works”Set the connection character set first so escaping follows the connection encoding. quote_string() returns the complete literal including its delimiters. For ordinary queries with user data, bind prepared statement parameters.
Troubleshooting
Section titled “Troubleshooting”- With a connection error, confirm the server is listening on the configured host and port and that the account permits connections from that host.
Exercise
Section titled “Exercise”Try a Unicode name containing an apostrophe. Compare the value returned by the server with the original and confirm both match.
Wrap-up
Section titled “Wrap-up”Continue with Set Byte Order with pack() and unpack().