Although this question is quite old, some topics have not actually been discussed, which should be presented here for others exploring the same thing as the OP.
To summarize everything below:
- Yes always use preparation instructions
- Yes uses PDO over mysqli over mysql. Thus, if you switch database systems, all you have to do is update the queries instead of queries, function calls and arguments, given that they support prepared statements.
- Always sanitize user-provided data despite using prepared statements with parameters
- Take a look at DBAL (Database Abstraction Layer) to make it easier to handle all of these factors and manipulate queries according to your needs.
There is a topic PDO :: ATTR_EMULATE_PREPARES that will increase the performance of calling cached queries in MySQL> = 5.1.21 when emulation is disabled, which is enabled by default. Value PHP will emulate the preparation before execution, sends it to the actual database. The time between emulated and non-emulated is usually insignificant if it does not work with an external database (and not with localhost), for example, in the cloud, which can have an abnormally high level of ping.
Caching depends on your MySQL settings in my.cnf, but MySQL optimization is beyond the scope of this publication.
<?php $pdo = new \PDO($connection_string); $pdo->setAttribute( \PDO::ATTR_EMULATE_PREPARES, false ); ?>
So keep this in mind, since mysqli_ does not provide an API to emulate the client side and will always use MySQL to prepare statements. http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
Despite the existence of similar functions, there are differences, and you may need functions that one API provides and the other does not. See the PHP link for choosing one API over another: http://www.php.net/manual/en/mysqlinfo.api.choosing.php
Thus, this is largely consistent with what you requested when defining your statements in applications, since cached requests will be cached on the MySQL server and do not need to be prepared for the entire application. Another advantage is that exceptions in your Query will be thrown in prepare () instead of execute (), which help in development to ensure that your queries are correct.
Regardless of whether there are real performance benefits when using training or not.
Another advantage of prepared statements works with transactions if you use InnoDB for MySQL. You can start a transaction, insert a record, get the last insert identifier, update another table, delete from another, and if something fails along the path that you can rollBack () before the transaction. Otherwise, copy the changes if you decide. For example, working with the new order and setting the last user serial number for the new order identifier and deleting the pending order, but the provided payment type did not meet the criteria for placing orders from the order_flags table, so you can rollBack () and show the user a friendly error message.
As for security, I'm pretty puzzled, no one has touched on this. When sending any user-provided data to ANY system, including PHP and MySQL, sanitize and standardize it. Yes, prepared statements do provide some security when it comes to data shielding, but it is NOT 100% bullet proof.
Therefore, always using prepared instructions is much more profitable than without real loss of performance, as well as some advantages with caching, but you still need to sanitize your data provided by the user. One of the steps is to cast the variables to the type of the required data type that you are working with. Using objects will make this even easier, since you are working within the same model for data types, and do not remember it every time you work with the same data.
To add to the above, you should study the level of database abstraction that PDO uses. For example, Doctrine DBAL: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html
Additional benefits of working with DBAL + PDO are that
- You can standardize and reduce the amount of work you have to do.
- Help disinfect user-submitted data
- Easily manage complex queries
- Using Nested Transactions
- Easily switch between databases
- Your code becomes more portable and can be used in other projects.
For example, I extended PDO and redefined the query (), fetchAll (), and fetch () methods so that they always use prepared statements and so that I can write SQL statements inside fetch () or fetchAll () instead of writing everything again. EG:
<?php $pdo = new PDOEnhanced( $connection ); $pdo->fetchAll( "SELECT * FROM foo WHERE bar = 'hi'", PDO::FETCH_OBJ ); //would automatically provide $stmt = $pdo->prepare( "SELECT * FROM foo WHERE bar=?" ); $stmt->execute( array( 'hi' ) ); $resultSet = $stmt->fetchAll( PDO::FETCH_OBJ ) ?>
For people offering the mysql_ * style, it’s much easier to just replace the mysqli_ * API. This is not true. Most of the mysql_ * functions were left or arguments changed in mysqli_ * See: http://php.net/manual/en/mysqli.summary.php
However, you can get the converter released by Oracle to facilitate the process: https://wikis.oracle.com/display/mysql/Converting+to+MySQLi
Keep in mind that this is a text parser of the source text and is not 100% accurate, so check the changes before combining them. It will also add a significant portion of the overhead for the generated global variables.