Replacing MySQL_* Functions with Pdo and Prepared Statements

Replacing mysql_* functions with PDO and prepared statements

Thanks for the interesting question. Here you go:

It escapes dangerous characters,

Your concept is utterly wrong.
In fact "dangerous characters" is a myth, there are none.
And mysql_real_escape_string escaping but merely a string delimiters. From this definition you can conclude it's limitations - it works only for strings.

however, it is still vulnerable to other attacks which can contain safe characters but may be harmful to either displaying data or in some cases, modifying or deleting data maliciously.

You're mixing here everything.

Speaking of database,

for the strings it is NOT vulnerable. As long as your strings being quoted and escaped, they cannot "modify or delete data maliciously".*
for the other data typedata - yes, it's useless. But not because it is somewhat "unsafe" but just because of improper use.

As for the displaying data, I suppose it is offtopic in the PDO related question, as PDO has nothing to do with displaying data either.

escaping user input

^^^ Another delusion to be noted!

a user input has absolutely nothing to do with escaping. As you can learn from the former definition, you have to escape strings, not whatever "user input". So, again:
- you have escape strings, no matter of their source
- it is useless to escape other types of data, no matter of the source.

Got the point?

Now, I hope you understand the limitations of escaping as well as the "dangerous characters" misconception.

It is to my understanding that using PDO/prepared statements is a much safer

Not really.

In fact, there are four different query parts which we can add to it dynamically:

a string
a number
an identifier
a syntax keyword.

so, you can see that escaping covers only one issue. (but of course, if you treat numbers as strings (putting them in quotes), when applicable, you can make them safe as well)

while prepared statements cover - ugh - whole 2 isues! A big deal ;-)

For the other 2 issues see my earlier answer, In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

Now, function names are different so no longer will my mysql_query, mysql_fetch_array, mysql_num_rows etc work.

That is another, grave delusion of PHP users, a natural disaster, a catastrophe:

Even when utilizing old mysql driver, one should never use bare API functions in their code! One have to put them in some library function for the everyday usage! (Not as a some magic rite but just to make the code shorter, less repetitive, error-proof, more consistent and readable).

The same goes for the PDO as well!

Now on with your question again.

but by using them does this eliminate the need to use something like mysql_real_escape_string?

YES.

But I think this is roughly the idea of what should be done to fetch a user from a database

Not to fetch, but to add a whatever data to the query!

you have to given a length after PDO:PARAM_STR if I'm not mistaken

You can, but you don't have to.

Now, is this all safe?

In terms of database safety there are just no weak spots in this code. Nothing to secure here.

for the displaying security - just search this site for the XSS keyword.

Hope I shed some light on the matter.

BTW, for the long inserts you can make some use of the function I wrote someday, Insert/update helper function using PDO

However, I am not using prepared statements at the moment, as I prefer my home-brewed placeholders over them, utilizing a library I mentioned above. So, to counter the code posted by the riha below, it would be as short as these 2 lines:

$sql  = 'SELECT * FROM `users` WHERE `name`=?s AND `type`=?s AND `active`=?i';
$data = $db->getRow($sql,$_GET['name'],'admin',1);

But of course you can have the same code using prepared statements as well.

* (yes I am aware of the Schiflett's scaring tales)

How to replace MySQL functions with PDO?

I see a lot of code posted on SO implementing my_sql functions. And comments from others (including myself) pressing the questioners to abandon MySQL functions and start using PDO or MySQLI. This post is here to help. You can refer to it as it provides explanation to why they are deprecated and what PDO is, plus a minimal code example to implement PDO.

First of all:

Conversion from mysql functions to PDO is not a simple case of search and replace. PDO is an Object Oriented Programming add on for the PHP language.
That means an other approach in writing the code as with the mysql functions. First why convert?

Why are mysql functions deprecated?

The mysql extension is ancient and has been around since PHP 2.0, released 15 years ago (!!); which is a decidedly different beast than the modern PHP which tries to shed the bad practices of its past. The mysql extension is a very raw, low-level connector to MySQL which lacks many convenience features and is thereby hard to apply correctly in a secure fashion; it's therefore bad for noobs. Many developers do not understand SQL injection and the mysql API is fragile enough to make it hard to prevent it, even if you're aware of it. It is full of global state (implicit connection passing for instance), which makes it easy to write code that is hard to maintain. Since it's old, it may be unreasonably hard to maintain at the PHP core level.

The mysqli extension is a lot newer and fixes all the above problems. PDO is also rather new and fixes all those problems too, plus more.

Due to these reasons* the mysql extension will be removed sometime in the future.

source Deceze

How to implement PDO

PDO offers one solution for connecting to multiple databases. This answer covers only MySQL and MSSQL servers.

Connecting to a MySQL database, prerequisites

This is fairly simple and doesn't require any pre set-up of PHP. Modern PHP installations are standard shipped with a module that allows PDO connections to MySQL servers.

The module is php_pdo_mysql.dll

Connecting to a MSSQL database, prerequisites

This is a more advanced set-up. You need php_pdo_sqlsrv_##_ts.dll or php_pdo_sqlsrv_##_nts.dll drivers. They are version specific hence the ##. At the moment of writing, Microsoft has released
official drivers for PHP 5.5.x. The 5.6 drivers aren't yet officially released by Microsoft, but are available as non-official builds by others.

The module is php_pdo_sqlsrv_##_ts.dll for the thread safe variant
The module is php_pdo_sqlsrv_##_nts.dll for the non-thread safe variant

Connecting to a database using PDO
To connect to a database you need to create a new PDO instance from the PDO construct.

$connection = new PDO(arguments);

The PDO constructor takes 1 required arguments and 3 optional.

DSN or Data Source Name, mostly this is a string containing information about the driver, host and database name. Since PHP 7.4 it can also include username and password.
Username
Password
Options

Connecting to MySQL

$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);

Let's take a look at $dsn: First it defines the driver (mysql). Then the database name and finally the host.

Connecting to MSSQL

$dsn = 'sqlsrv:Server=127.0.0.1;Database=databasename';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);

Let's take a look at $dsn: First it defines the driver (sqlsrv). Then the host and finally the database name.

When you create the instance a connection is made to the database. You only have to do this once during the execution of a PHP script.

You need to wrap the PDO instance creation in a try-catch clause. If the creation fails a back trace is shown revealing critical information about your application, like username and password. To avoid this catch the errors.

try 
{
    $connection = new PDO($dsn, $user, $password);
}
catch( PDOException $Exception ) 
{   
     echo "Unable to connect to database.";
     exit;
}

To throw errors returned by your SQL server add this options to your PDO instance using setAttribute: $connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Performing queries

PDO uses prepared statements. This is a real difference between PDO's approach and mysql functions. The latter was very susceptible to SQL-INJECTION. One would build a query like this:

$SQL = 'SELECT ID FROM users WHERE user = '.$username ;

When a malicious website or person posts the username injector; DROP TABLE users. The results will be devastating. You needed to proof your code by escaping and encapsulating strings and variables with quotes. This had to be done
for every query. On larger websites or poorly maintained code the risk of having a form that allowed SQL injection could become very high. Prepared statements eliminates the chance of first tier SQL injection like the example above.

The PDO drivers act as a man-in-the-middle between your PHP-server and database server, called a data-access abstraction layer. It doesn't rewrite your SQL queries, but do offer a generic way to connect to multiple database types
and handles the insertion of variables into the query for you. Mysql functions constructed the query on execution of the PHP code. With PDO the query actually gets build on the database server.

A prepared SQL example:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';

Note the difference; Instead of a PHP variable using $ outside the string, we introduce a variable using : within the string. Another way is:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = ?';

How to perform the actual query

Your PDO instance provides two methods of executing a query. When you have no variables you can use query(), with variables use prepare(). query() is immediately executed upon calling. Please note the object oriented way of the call (->).

$result = $connection->query($SQL);

The prepare method

The prepare method takes two arguments. The first is the SQL string and the second are options in the form of an Array. A basic example

$connection->prepare($SQL, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

In our SQL string example we've used a named variable called :username. We still need to bind a PHP variable, integer or string to it. We can do this in two ways. Either build an array containing the named variables as key or use the method bindParam or bindValue.
I will explain the array variant and the method bindValue for the sake of simplicity.

Array

You can do something like this for named variables, where you provide the variable as array key:

$queryArguments = array(':username' => $username);

And this for indexed variables (?):

$queryArguments = array($username);

When you have added all the variables you need you can call upon the method execute() to perform the query. Thereby passing the array as argument to the function execute.

$result = $connection->execute($queryArguments);

bindValue

The bindValue method allows you to bind values to the PDO instance. The method takes two required arguments and one optional. The optional arguments set the data-type of the value.

For named variables:

$connection->bindValue(':username', $username);

For indexed variables:

$connection->bindValue(1, $username);

After binding the values to the instance, you can call upon execute without passing any arguments.

$result = $connection->execute();

NOTE: You can only use a named variable once! Using them twice will result in a failure to execute the query. Depending on your settings this will or will not throw an error.

Fetching the results

Again I will only cover the basics for fetching results from the returned set. PDO is a fairly advanced add-on.

Using fetch and fetchAll

If you did a select query or executed a stored procedure that returned a result set:

fetch

fetch is a method that could take up to three optional arguments. It fetches a single row from the result set. By default it returns an array containing the column names as keys and indexed results.
Our example query could return something like

ID      EMAIL
1       someone@example.com

fetch will return this as:

Array
(
    [ID] => 1
    [0] => 1
    [EMAIL] => someone@example.com
    [1] => someone@example.com
)

To echo all output of a result set:

while($row = $result->fetch())
{
    echo $row['ID'];
    echo $row['EMAIL'];
}

There are other options you can find here: fetch_style;

fetchAll

Fetches all rows in a single array. Using the same default option as fetch.

$rows = $result->fetchAll();

If you used a query that didn't return results like a insert or update query you can use the method rowCount to retrieve the amount of rows affected.

A simple class:

class pdoConnection {
    public $isConnected;

    protected $connection;

    public function __construct($dsn, $username, $password, $options = array()) {
        $this->isConnected = true;
        try {
            $this->connection = new PDO($dsn, $username, $password, $options);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); //sets the default to return 'named' properties in array.
        } catch (PDOException $e) {
            $this->isConnected = false;
            throw new Exception($e->getMessage());
        }
    }

    public function disconnect() {
        $this->connection = null;
        $this->isConnected = false;
    }

    public function query($SQL) {
        try {
            $result = $this->connection->query($SQL);
            return $result;
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage());
        }
    }

    public function prepare($SQL, $params = array()) {
        try {
            $result = $this->connection->prepare($SQL);
            $result->execute($params);
            return $result;
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage());
        }
    }
}

How to use:

$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$db = new pdoConnection($dsn, $user, $password);

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
$result = $db->prepare($SQL, array(":username" => 'someone'));

while($row = $result->fetch())
{
    echo $row['ID'];
    echo $row['EMAIL'];
}

Using MySQL functions in PHP PDO prepared statements

I would not pass functions as the bound params:

$sth = $dbh->prepare("INSERT INTO pdo (namespace, count, teststring) VALUES (?, ?, NOW())");

$_a = 'Wishy-washy';
$_b = 123;

$sth->execute(array($_a, $_b));

Where to put PDO prepared statements in a DB_Connector class - in the constructor or the functions?

This answer is based in both my experience and humble opinion, but I'll try to elaborate my arguments so it isn't just some random guy's opinion.

I don't think the database connection must be the core object in your application, let alone the only one. It'd expect to see an entirely different class for the user, so you can later have further classes for everything else. Otherwise, your application will eventually consist of a single class in a 5000 line file and your class will not be suitable to track entity data at instance level and you'll need to pass variables around in method calls. That's pretty much procedural code in OOP dress.

Also, I don't that making your User class inherits from Database (something pretty frequent nonetheless) is practical at all. Interlacing the database connection and the business logic objects doesn't really simplify application design and actually makes some parts harder.

The design of the database layer itself is pretty much standardised:

One connection per application (or more... you may need to connect to several sources!)
One statement per query.

This is exactly how PDO works.

Given that, it's easier to make the database classes just one more dependency of your entities rather than their grandparent. The injection of this dependency can be done by different means:

Make it a class property:

public function __construct(\PDO $connection)
{
    $this->connection = $connection;
}

Pass it to the methods they actually needed (if not many of them):

public function getOrders(\PDO $connection)
{
    $stmt = $connection->prepare('SELECT ...');
}

... or use one of those fancy dependency injection containers you can find at Packagist.

Be aware that there're also object-relational mapping (ORM), active record pattern... Those are entirely different families of solutions which may suit your needs or not depending on your use case, but not what I'm describing here.

Said that, it becomes obvious that you prepare the statements at the exact point where you need them. This design doesn't even allow otherwise ;-)

Should I really be using PDO and prepared statements?

You could, but PDO and prepared statements are the absolute safest. Could you do it by hand and use the mysql_real_escape_string() function? Sure. In fact, your output might look identical. But in the end, the code that PDO would require would be a hell of a lot shorter than the code if you had done it manually.

Also, if you aren't using prepared statements, you run the risk of human error: say you forget to escape a value or sanitize an input. Mixed in with all of your other code, the one line that isn't properly sanitizing could crop up to be a nightmare down the road.

Hope this helps!

Can I blindly replace all mysql_ functions with mysqli_?

The short answer is no, the functions are not equivalent.

The good news is there is a converter tool that will help you if you've got a lot of calls/projects to change. This will allow your scripts to work right away.

https://github.com/philip/MySQLConverterTool

It's a forked version of the Oracle original version, and it's kosher.

That said, it's not too difficult to update your code, and you might want to migrate to an object orientated methodology anyway ...

1) The Connection

For all intents and purposes, you need a new connection function that saves the connection as a PHP variable, for example;

$mysqli = new mysqli($host, $username, $password, $database);

Notice I've saved the connection to $mysqli. You can save to $db or whatever you like, but you should use this throughout your code to reference the connection.

Remember to enable error reporting for mysqli before opening the connection;

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

2) The Query

Note: You should protect against SQL injection with prepared statements, which are available in MySQLi. Take a look at How can I prevent SQL injection in PHP?, but I'm just going to cover the basics here.

You now have to include the connection as an argument in your query, and other mysqli_ functions. In procedural code it's the first argument, in OO you write it like a class method.

Procedural:

$result = mysqli_query($mysqli, $sql);

OO:

$result = $mysqli->query($sql);

3) Fetch Result

The fetching of the result is similar to the old mysql_ function in procedural;

while ($row = mysqli_fetch_assoc($result))

but as $result is now an object in mysqli, you can use the object function call;

while ($row = $result->fetch_assoc())

4) Close Connection

So as before, you need to include the connection in the close function; as an argument in procedural;

mysqli_close($mysqli);

and as the object that you run the function on in OO;

$mysqli->close();

I would be here forever if I went through them all, but you get the idea. Take a look at the documentation for more information. Don't forget to convert any connection close, result release, or error and row counting functions you have.

The basic rule of thumb is for functions that use the database connection, you need to include it in the function now (either as the first argument in procedural, or the object you use to call the function in OO), or for a result set you can just change the function to mysqli_ or use the result set as the object.

php pdo prepare repetitive variables

The simple answer is: You can't. PDO uses an abstraction for prepared statements which has some limitations. Unfortunately this is one, you have to work-around using something like

$query = "UPDATE users SET firstname = :name1 WHERE firstname = :name2";
$stmt = $dbh -> prepare($query);
$stmt -> execute(array(":name1" => "Jackie", ":name2" => "Jackie"));

In certain cases, such as emulated prepared statements with some versions of the PDO/MySQL driver, repeated named parameters are supported; however, this shouldn't be relied upon, as it's brittle (it can make upgrades require more work, for example).

If you want to support multiple appearances of a named parameter, you can always extend PDO and PDOStatement (by classical inheritance or by composition), or just PDOStatement and set your class as the statement class by setting the PDO::ATTR_STATEMENT_CLASS attribute. The extended PDOStatement (or PDO::prepare) could extract the named parameters, look for repeats and automatically generate replacements. It would also record these duplicates. The bind and execute methods, when passed a named parameter, would test whether the parameter is repeated and bind the value to each replacement parameter.

Note: the following example is untested and likely has bugs (some related to statement parsing are noted in code comments).

class PDO_multiNamed extends PDO {
    function prepare($stmt) {
        $params = array_count_values($this->_extractNamedParams());
        # get just named parameters that are repeated
        $repeated = array_filter($params, function ($count) { return $count > 1; });
        # start suffixes at 0
        $suffixes = array_map(function ($x) {return 0;}, $repeated);
        /* Replace repeated named parameters. Doesn't properly parse statement,
         * so may replacement portions of the string that it shouldn't. Proper
         * implementation left as an exercise for the reader.
         *
         * $param only contains identifier characters, so no need to escape it
         */
        $stmt = preg_replace_callback(
            '/(?:' . implode('|', array_keys($repeated)) . ')(?=\W)/', 
            function ($matches) use (&$suffixes) {
                return $matches[0] . '_' . $suffixes[$matches[0]]++;
            }, $stmt);
        $this->prepare($stmt, 
                       array(
                           PDO::ATTR_STATEMENT_CLASS => array('PDOStatement_multiNamed', array($repeated)))
            );
    }

    protected function _extractNamedParams() {
        /* Not actually sufficient to parse named parameters, but it's a start.
         * Proper implementation left as an exercise.
         */
        preg_match_all('/:\w+/', $stmt, $params);
        return $params[0];
    }
}

class PDOStatement_multiNamed extends PDOStatement {
    protected $_namedRepeats;

    function __construct($repeated) {
        # PDOStatement::__construct doesn't like to be called.
        //parent::__construct();
        $this->_namedRepeats = $repeated;
    }

    /* 0 may not be an appropriate default for $length, but an examination of
     * ext/pdo/pdo_stmt.c suggests it should work. Alternatively, leave off the
     * last two arguments and rely on PHP's implicit variadic function feature.
     */
    function bindParam($param, &$var, $data_type=PDO::PARAM_STR, $length=0, $driver_options=array()) {
        return $this->_bind(__FUNCTION__, $param, func_get_args());
    }

    function bindValue($param, $var, $data_type=PDO::PARAM_STR) {
        return $this->_bind(__FUNCTION__, $param, func_get_args());
    }

    function execute($input_parameters=NULL) {
        if ($input_parameters) {
            $params = array();
            # could be replaced by array_map_concat, if it existed
            foreach ($input_parameters as $name => $val) {
                if (isset($this->_namedRepeats[$param])) {
                    for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
                        $params["{$name}_{$i}"] = $val;
                    }
                } else {
                    $params[$name] = $val;
                }
            }
            return parent::execute($params);
        } else {
            return parent::execute();
        }
    }

    protected function _bind($method, $param, $args) {
        if (isset($this->_namedRepeats[$param])) {
            $result = TRUE;
            for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
                $args[0] = "{$param}_{$i}";
                # should this return early if the call fails?
                $result &= call_user_func_array("parent::$method", $args);
            }
            return $result;
        } else {
            return call_user_func_array("parent::$method", $args);
        }
    }
}

Replacing MySQL_* Functions with Pdo and Prepared Statements