Mysqli_Prepare VS Pdo

mysqli_prepare vs PDO

Yes, writing a generic bind-this-array-into-a-query in Mysqli is a royal PITA. I eventually got it to work when I was coding Zend Framework's mysqli adapter, but it took a lot of work. You're welcome to take a look at the code. I see one chief difference, here's how I did the refs:

$stmtParams = array();
foreach ($params as $k => &$value) {
    $stmtParams[$k] = &$value;
}
call_user_func_array(
    array($this->_stmt, 'bind_param'), // mysqli OO callback
    $stmtParams
);

This is slightly different than yours. I wonder if in your code the ref operator & binds more tightly than the array index [] operator.

Note I also had to use the ref operator both in the foreach and in the assignment. I never quite understood why, but this was the only way it would work. PHP refs are pretty mysterious and hard to understand.

This may not be a viable suggestion if you're stuck with an environment that has Mysqli enabled but not PDO, but you should really consider using PDO instead. PDO takes care of a lot of that work for you; you can simply pass an array of values to PDOStatement::execute() for a prepared query with parameters. For me, it was far easier to use PDO for this particular use than mysqli.

$pdoStmt->execute( array('joe@gmail.com','Password') );  // it's that easy

P.S.: I hope you're not storing passwords in plaintext.

What is the difference between PDO and MySQLi prepared statements?

The difference is below:-

Mysqli is only for the MySQL database. PDO supports other database using the same functions.
Mysqli can be used in either an object-oriented style or a procedural style. PDO is always object-oriented.
Mysqli supports prepared statements with ? placeholders for parameters. PDO supports both ? placeholders and also named placeholders, like :columnName.
Mysqli requires that you use a function to bind each parameter value to the prepared statement. PDO also allows you to simply pass an array of parameter values as you execute the prepared statement.

MySQLi: query VS prepare

Prepared statements are preferable to plain SQL queries when you are using parameters to dynamically generate the query. In you example, your SQL contains no variables, so using a plain query or prepared statement are functionally equivalent.

When you must change the values of parameters, in the WHERE clause, for example, then prepared statements will give you added security:

...
WHERE col1 = ? AND col2 = ?

But when your query is simple and fixed, it may require less code to use $mysqli->query($sql) along with fetch_assoc(). Using direct queries rather than prepared statements is not a universally bad practice, as some might have you believe. When your query requires parameterization, or when the same query must be compiled and executed repeatedly, then you'll benefit from the prepared statement.

How to convert PDO to MYSQLi

Well, first in a simple select query you really don't need the prepared statement. but if you wanna do I think this would help you.

$query = "SELECT shape FROM inventory";
  $statement = mysqli_prepare($db, $query);
  mysqli_stmt_execute($statement);

  while(mysqli_stmt_fetch($statement)){
....
}

Can someone clearly explain why mysqli_prepare()/bind_param() is better than real_escape_string()?

What you are reading, that you need to use mysqli_prepare() and mysqli_bind_param() functions to "properly escape your MySQL queries" is wrong.

It is true that if you use mysqli_prepare() and mysqli_bind_param(), you needn't (and shouldn't) "escape" the values supplied as bind parameters. So, in that sense, there's some truth in what you are reading.

It's only when unsafe variables are included in the SQL text (the actual text of the query) that you need to "properly escape" the variables, usually by wrapping the variables in mysqli_real_escape_string() function calls.

(We note that it's possible to make of use of prepared statements and still include un-escaped variables in the SQL text, rather than passing the variable values as bind_parameters. That does sort of defeats the purpose of using prepared statements, but the point is, either way, you can write code that is vulnerable.

MySQL now supports "server side" prepared statements (if the option is enabled in the connection), and that's a performance optimization (in some cases) of repeated executions of identical SQL text. (This has been long supported in other databases, such as Oracle, where making use of prepared statements has been a familiar pattern for, like, since forever.)

Q: Did they implement [prepared statements] so that people wouldn't forget to escape values before sending them in a query?

A: Based on the number of examples of code vulnerable to SQL Injection when not using prepared statements, despite the documentation regarding mysql_real_escape_string() function, you'd think that certainly would be sufficient reason.

I think one big benefit is that when we're reading code, we can see a SQL statement as a single string literal, rather than a concatenation of a bunch of variables, with quotes and dots and calls to mysql_real_escape_string, which isn't too bad with a simple query, but with a more complex query, it is just overly cumbersome. The use of the ? placeholder makes for a more understandable SQL statement,... true, I need to look at other lines of code to figure out what value is getting stuffed there. (I think the Oracle style named parameters :fee, :fi, :fo, :fum is preferable to the positional ?, ?, ?, ? notation.) But having STATIC SQL text is what is really the benefit.

Q: Or is it somehow faster?

As I mentioned before, the use of server side prepared statements can be and advantage in terms of performance. It's not always the case that it's faster, but for repeated execution of the same statement, where the only difference is literal values (as in repeated inserts), it can provide a performance boost.

Q: Or should I use this method when I intend to use the same query repeatedly (since a mysqli_stmt can be reused) and use the traditional method in other cases?

That's up to you. My preference is for using STATIC SQL text. But this really comes from a long history of using Oracle, and using the same pattern with MySQL fits naturally. (Albeit, from Perl using the DBI interface, and Java using JDBC and MyBATIS, or other ORMs (Hibernate, Glassfish JPA, et al.)

Following the same pattern just feels natural in PHP; the introduction of mysqli_ and PDO are a welcome relief from the arcane (and abused) mysql_ interface.

Good code can be written following either pattern. But I challenge you to think ahead, about more complex SQL statements, and whether the choice to use mysqli_real_escape_string() and concatenating together a dynamic string to be executed, rather than using static SQL text and bind parameters, might make reading, and deciphering, the actual SQL being executed more complicated for the soul that finds themselves maintaining code they didn't write.

I think studies have shown that code is read ten times more than it is written, which is why we strive to produce readable, understandable code, even if that means more lines of code. (When each statement is doing a single identifiable thing, that's usually easier for me to understand than reading a jumble of concatenated function calls in one complicated statement.

What's this PDO Code in mysqli?

As said, I recommend using the PDO extension. But if you choose to use mysqli instead, then use the object-oriented mysqli instead of the procedural one. On php.net each mysqli function is presented in both ways.

The password should be strongly encrypted. My recommendation: the password_hash function - either with the PASSWORD_BCRYPT option (a constant defining the Blowfish hashing algorithm), or with the PASSWORD_ARGON2I option (constant defining the Argon2 hashing algorithm and introduced as of PHP 7.2.0). So, you should first save new user credentials in the form of a password hash (a string of minimum 60 aleatory characters) - in the users table. With a code similar to this one:

signup.php:

$username = $_POST['username'];
$password = $_POST['password'];

// Create a hash from a posted password.
$passwordHash = password_hash($password, PASSWORD_BCRYPT);

$sql = 'INSERT INTO users (username, password) VALUES (?, ?)';

$statement = $connection->prepare($sql);
$statement->bind_param('ss', $username, $passwordHash);
$statement->execute();

//...

For a clear view regarding your question here's an extended example of a login page - using my own naming and coding conventions. How you decide to adapt it, e.g. to process the results, is up to your logical scheme and system - I'm not familiar with Android. The code contains a server-side credentials validation part too. For proper error reporting see this article. Don't forget to change the db credentials.

connection.php:

<?php

/*
 * This page contains the code for creating a mysqli connection instance.
 */

// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');

/*
 * Enable internal report functions. This enables the exception handling,
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
 * (mysqli_sql_exception).
 *
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
 *
 * @link http://php.net/manual/en/class.mysqli-driver.php
 * @link http://php.net/manual/en/mysqli-driver.report-mode.php
 * @link http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/*
 * Create a new db connection.
 *
 * @see http://php.net/manual/en/mysqli.construct.php
 */
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

login.php:

<?php

require 'connection.php';

/*
 * ================================
 * Operations upon form submission.
 * ================================
 */
if (isset($_POST['submit'])) {
    /*
     * =======================
     * Read the posted values.
     * =======================
     */
    $username = isset($_POST['username']) ? $_POST['username'] : '';
    $password = isset($_POST['password']) ? $_POST['password'] : '';

    /*
     * ===========================
     * Validate the posted values.
     * ===========================
     */
    // Validate the username.
    if (empty($username)) {
        $errors[] = 'Please provide a username.';
    } /* Other validations here using elseif statements */

    // Validate the password.
    if (empty($password)) {
        $errors[] = 'Please provide a password.';
    } /* Other validations here using elseif statements */

    /*
     * ======================
     * Check the credentials.
     * ======================
     */
    if (!isset($errors)) { // No errors yet.
        /*
         * The SQL statement to be prepared. Notice the so-called markers,
         * e.g. the "?" signs. They will be replaced later with the
         * corresponding values when using mysqli_stmt::bind_param.
         *
         * @link http://php.net/manual/en/mysqli.prepare.php
         */
        $sql = 'SELECT username, password
                FROM users
                WHERE username = ?
                LIMIT 1';

        /*
         * Prepare the SQL statement for execution.
         *
         * @link http://php.net/manual/en/mysqli.prepare.php
         */
        $statement = $connection->prepare($sql);

        /*
         * Bind variables for the parameter markers (?) in the
         * SQL statement that was passed to prepare(). The first
         * argument of bind_param() is a string that contains one
         * or more characters which specify the types for the
         * corresponding bind variables.
         *
         * @link http://php.net/manual/en/mysqli-stmt.bind-param.php
         */
        $statement->bind_param('s', $username);

        /*
         * Execute the prepared SQL statement.
         * When executed any parameter markers which exist will
         * automatically be replaced with the appropriate data.
         *
         * @link http://php.net/manual/en/mysqli-stmt.execute.php
         */
        $statement->execute();

        /*
         * Get the result set from the prepared statement.
         *
         * NOTA BENE:
         * Available only with mysqlnd ("MySQL Native Driver")! If this
         * is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
         * PHP config file (php.ini) and restart web server (I assume Apache) and
         * mysql service. Or use the following functions instead:
         * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
         *
         * @link http://php.net/manual/en/mysqli-stmt.get-result.php
         * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
         */
        $result = $statement->get_result();

        /*
         * Fetch the credentials into an associative array.
         * If no record is found, the operation returns NULL.
         */
        $credentials = $result->fetch_array(MYSQLI_ASSOC);

        if (isset($credentials) && $credentials) { // Record found.
            $fetchedUsername = $credentials['username'];
            $fetchedPasswordHash = $credentials['password'];

            /*
             * Compare the posted username with the one saved in db and the posted
             * password with the password hash saved in db using password_hash.
             *
             * @link https://secure.php.net/manual/en/function.password-verify.php
             * @link https://secure.php.net/manual/en/function.password-hash.php
             */
            if (
                    $username === $fetchedUsername &&
                    password_verify($password, $fetchedPasswordHash)
            ) {
                header('Location: welcome.html');
                exit();
            } else {
                $errors[] = 'Invalid credentials. Please try again.';
            }
        } else {
            $errors[] = 'No credentials found for the given user.';
        }
    }
}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo - Login</title>

        <script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>

        <script type="text/javascript">
            $(document).ready(function () {
                $('#username').focus();
            });

            function validateForm() {
                return true;
            }
        </script>

        <style type="text/css">
            body {
                padding: 30px;
            }

            label {
                display: block;
                font-weight: 400;
            }

            input[type="text"],
            input[type="password"] {
                display: block;
                margin-bottom: 20px;
            }

            button {
                display: block;
                padding: 7px 10px;
                background-color: #8daf15;
                color: #fff;
                border: none;
            }

            .messages {
                margin-bottom: 20px;
            }

            .messages .error {
                color: #c00;
            }
        </style>
    </head>
    <body>

        <div class="messages">
            <?php
            if (isset($errors)) {
                foreach ($errors as $error) {
                    ?>
                    <div class="error">
                        <?php echo $error; ?>
                    </div>
                    <?php
                }
            }
            ?>
        </div>

        <div class="form-login">
            <form name="credentials" action="" method="post" onsubmit="return validateForm();">
                <label for="username">Username:</label>
                <input type="text" id="username" name="username" value="<?php echo isset($username) ? $username : ''; ?>">

                <label for="password">Password:</label>
                <input type="password" id="password" name="password" value="<?php echo isset($password) ? $password : ''; ?>">

                <button type="submit" name="submit" value="submit">
                    Submit
                </button>
            </form>
        </div>

    </body>
</html>

The table structure used for testing:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The data used for testing:

This data would be saved by running the signup.php code. Each password hash here (e.g. each value of the password column) is the encrypted representation of the corresponding value of the username column. For example, the first hash represents the string (e.g. the password) "demo1".

INSERT INTO `users` (`id`, `username`, `password`)
VALUES
    (1, 'demo1', '$2y$10$ZzULeTfsMwBj6DwpsfzxPu0irOrkL.l7rkimPkpcojL4RAMLwEZkW'),
    (2, 'demo2', '$2y$10$bpLOz4ur4wdVs4RN9ZatGekmynMhgOAdkwBchRLAf2t8hwc9Kkh7K');

What does mysqli_prepare() really do? Why should it be called by user and not handled internally by mysqlite3?

The difference between execute() and prepare() is not that the prepare() method has some internal flag like "secure=true;" which will be set and magically you are save for SQL injections. In fact, you can have SQL injections with prepare() as well when you use it wrong:

$stmt = $db->prepare('SELECT password FROM user WHERE username = "'.$username.'"');
$stmt->execute();
// ...

The point of prepared statements is that the values/arguments/variables for the SQL query are send separated from the actual SQL query to the MySQL server. This way the values/arguments/variables cannot change the SQL query you are trying to send. This prevents SQL injections where the inputs contain values like "='' OR 1 = 1 --".

The prepared statement is building a data structure where you set the values for the prepared statement separated via the additional API calls like bind_param(). The SQL server will use the "prepared" statement and use the values received from bind_param(). The query has been read, analyzed, well, it has been "prepared", and is fixed now (for the duration of this prepared statement) before even the first value with the potential dangerous data has been read. The WHERE condition cannot be altered or weakened, another SQL query cannot be appended or other rows/columns/tables can be edited.

Because of this, you cannot just redirect the execute() call internally to prepare() to be safe, because then you are missing the bind_param() calls. That being said: Use execute() when you have a fixed SQL query without any variables and/or user inputs and use prepare() for prepared statements, where you have an SQL query which depends on variables and/or user inputs.

Are PDO prepared statements sufficient to prevent SQL injection?

The short answer is YES, PDO prepares are secure enough if used properly.

I'm adapting this answer to talk about PDO...

The long answer isn't so easy. It's based off an attack demonstrated here.

The Attack

So, let's start off by showing the attack...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Selecting a Character Set
```
$pdo->query('SET NAMES gbk');
```
For this attack to work, we need the encoding that the server's expecting on the connection both to encode ' as in ASCII i.e. 0x27 and to have some character whose final byte is an ASCII \ i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbk and sjis. We'll select gbk here.
Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.
The Payload
The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string ¿'. Note that in latin1 and gbk, 0x27 on its own is a literal ' character.
We have chosen this payload because, if we called addslashes() on it, we'd insert an ASCII \ i.e. 0x5c, before the ' character. So we'd wind up with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...
$stmt->execute()
The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.
The C API call to mysql_real_escape_string() differs from addslashes() in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.
Therefore the call to mysql_real_escape_string() inserts the backslash, and we have a free hanging ' character in our "escaped" content! In fact, if we were to look at $var in the gbk character set, we'd see:
```
縗' OR 1=1 /*
```
Which is exactly what the attack requires.
The Query
This part is just a formality, but here's the rendered query:
```
SELECT * FROM test WHERE name = '縗' OR 1=1 /*' LIMIT 1
```

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

The Simple Fix

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The Correct Fix

The problem here is that we used SET NAMES instead of C API's mysql_set_charset(). Otherwise, the attack would not succeed. But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command!
It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

This is provided we are using a MySQL release since 2006. If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

The Saving Grace

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that instead—but it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)—so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

Safe Examples

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->query('SET NAMES gbk');
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));

Because we've turned off emulated prepared statements.

$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));

Because we've set the character set properly.

$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

Wrapping Up

If you:

Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) AND PDO's DSN charset parameter (in PHP ≥ 5.3.6)

Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

Enable NO_BACKSLASH_ESCAPES SQL mode

You're 100% safe.

Otherwise, you're vulnerable even though you're using PDO Prepared Statements...

Addendum

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

Mysqli_Prepare VS Pdo