Skip to content
CoursesWeb Forum

How do I use prepared statements with mysqli or PDO in PHP?

Staff answerWritten by Callum Iwu · MySQL Queries from PHP0 replies

Short answer

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.

Written by Callum Iwu · Moderator: PHP, MySQL, Node.js and AI coding tools ·
CiteCoursesWeb 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.
  1. Moderator: 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'='1 as 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 s in bind_param means the value is a string. Use i for integers and d for 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 :name
    Binding values bind_param with type letters Pass an array to execute
    Good 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

    1. Quotes around the placeholder. Write WHERE email = ?, not WHERE email = '?'. With quotes the question mark is plain text.
    2. 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.
    3. LIKE searches. Put the percent signs in the value, not the SQL: $stmt->execute(['%' . $term . '%']); with WHERE 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.
    4. Building part of the query from input anyway. One concatenated piece undoes the protection for the whole query.

    Check this in order

    1. Is the connection working? If not, see What does mysqli_query() expects parameter 1 to be mysqli mean?.
    2. Does the number of placeholders match the number of values you bind?
    3. For mysqli, does the type string have one letter per value, in order?
    4. Is error reporting on (mysqli_report or ERRMODE_EXCEPTION) so a failed prepare shows its real message?
    5. 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.
    0
    ReplyLink

Plain text or simple Markdown. Links are nofollow. Your email is never shown.

CoursesWeb Forum. Questions and answers on learning web development: HTML, CSS, JavaScript, PHP, MySQL, Node.js and AI coding help.