How do I use prepared statements with mysqli or PDO in PHP?
Write the SQL with a placeholder where each value goes, prepare it, bind the values separately, then execute. The database treats the values as data, never as SQL, which blocks SQL injection. Use a question mark with mysqli, or a question mark or a name with PDO. Never join user input into the SQL string.
Cite
CoursesWeb Forum, "How do I use prepared statements with mysqli or PDO in PHP?", https://coursesweb.net/forum/t/how-do-i-use-prepared-statements-with-mysqli-or-pdo-in-php/, accessed 2026-11-19.- Callum IwuStaffModerator: PHP, MySQL, Node.js and AI coding tools ·
The unsafe way, and what replaces it
This is the pattern to stop using:
<?php // UNSAFE: do not do this $result = $mysqli->query("SELECT id, name FROM users WHERE email = '" . $_POST['email'] . "'");If a visitor types
x' OR '1'='1as the email, their text becomes part of your SQL and changes what it does. The OWASP SQL Injection Prevention Cheat Sheet calls parameterized queries the primary defense, and says manual escaping is strongly discouraged.Prepared statements with mysqli
<?php mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $mysqli = new mysqli('localhost', 'YOUR_DB_USER', 'YOUR_DB_PASSWORD', 'YOUR_DB_NAME'); $mysqli->set_charset('utf8mb4'); $email = $_POST['email'] ?? ''; $stmt = $mysqli->prepare('SELECT id, name FROM users WHERE email = ?'); $stmt->bind_param('s', $email); $stmt->execute(); $user = $stmt->get_result()->fetch_assoc(); if ($user) { echo htmlspecialchars($user['name']); }The
sinbind_parammeans the value is a string. Useifor integers anddfor decimals. An insert with three values:<?php $stmt = $mysqli->prepare('INSERT INTO posts (title, body, author_id) VALUES (?, ?, ?)'); $stmt->bind_param('ssi', $title, $body, $authorId); $stmt->execute();The mysqli prepare manual page describes the question mark markers and says they must be bound to variables before the statement runs.
get_result()needs the mysqlnd driver, which most current hosts use.Prepared statements with PDO
PDO is a database layer that works with several database types. Its prepared statements use the same idea, with named placeholders if you prefer.
<?php $pdo = new PDO( 'mysql:host=localhost;dbname=YOUR_DB_NAME;charset=utf8mb4', 'YOUR_DB_USER', 'YOUR_DB_PASSWORD', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ] ); $stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = :email'); $stmt->execute(['email' => $_POST['email'] ?? '']); $user = $stmt->fetch();The PDO manual on prepared statements says parameters do not need to be quoted because the driver handles it, and that an application using only prepared statements avoids SQL injection, unless other parts of the query are built from unescaped input.
Which one to pick
mysqli PDO Databases MySQL and MariaDB only Many, including MySQL Placeholders ?only?or:nameBinding values bind_paramwith type lettersPass an array to executeGood for Staying with MySQL, small sites Cleaner code, switching databases later Both are safe when used correctly. Pick one and use it throughout a project.
Mistakes beginners make
- Quotes around the placeholder. Write
WHERE email = ?, notWHERE email = '?'. With quotes the question mark is plain text. - Placeholders for table or column names. They only stand in for values. The mysqli manual states markers are not permitted for identifiers. For a sort column, compare the user's choice against an allow-list in your code and use only the matching fixed word. OWASP recommends exactly this for names and ASC or DESC.
- LIKE searches. Put the percent signs in the value, not the SQL:
$stmt->execute(['%' . $term . '%']);withWHERE name LIKE ?. The PDO manual shows'%?%'inside the SQL as an invalid use. Our CoursesWeb page on WHERE with LIKE in PHP and MySQL shows the query side. - Building part of the query from input anyway. One concatenated piece undoes the protection for the whole query.
Check this in order
- Is the connection working? If not, see What does mysqli_query() expects parameter 1 to be mysqli mean?.
- Does the number of placeholders match the number of values you bind?
- For mysqli, does the type string have one letter per value, in order?
- Is error reporting on (
mysqli_reportorERRMODE_EXCEPTION) so a failed prepare shows its real message? - Did you validate the input first, as in How do I handle GET and POST form data safely in PHP?? Validation helps, but it complements binding and never replaces it.
- Quotes around the placeholder. Write
Write something first.
Give people something to work with: at least 30 words on what happened and what you tried.
That is too long. Keep it under 6,000 characters.
Write the question as the title, 15 to 140 characters, no links.
Pick a category.
Add a name (2 to 40 characters, no links).
That email address does not look right.
That was quick. Read the thread, then try again.
The form expired. Reload the page and post again.
Something went wrong with the form. Reload and try again.
Please complete the check and post again.
Limit reached for now. Try again later.
This thread is closed to new replies.
This thread no longer accepts replies.
Something went wrong with the form. Reload and try again.
Post a reply or question first (name and email), then this browser can vote, edit and accept answers.
You cannot vote on your own post.
Only the author (within 30 days) or the forum team can do that.
That email already has an account. Use your sign-in link instead.
New accounts are paused for the moment. Try again later.
That was already posted.