The goal of the question is to have one or more well-crafted answers to serve as a reference when using the extension mysql_*
in code.
Answering questions here on Stackoverflow I am surprised by the number of users who continue to maintain code that includes the API or extension mysql_*
to handle the data, despite the fact that in the PHP Manual it says the following:
This extension is deprecated as of PHP 5.5.0, so it has been removed as of PHP 7.0.0. The mysqli or PDO_MySQL extension should be used instead . See also the MySQL API Overview for help choosing a MySQL API.
That means the functions:
mysql_result
mysql_select_db
mysql_num_rows
mysql_connect
mysql_db_query
mysql_fetch_assoc
mysql_*... todas las que empiecen así ...
.
All of these features, and many others listed here , should be avoided.
To add insult to injury, the functions are almost identical to those of the extension mysqli
, which is recommended in conjunction with PDO. The only difference between the deprecated functions listed above and MySQLi's is the letter i
before the underscore _
.
So the question is this: What are the main serious reasons why I should stop using the extension mysql_*
to query my data?
PS: I am willing to reward one or several good answers.
The main drawbacks of functions
mysql_*
:mysql_real_escape_sting
,addslashes
orhtmlspecialchars
solves that problem, but it has been proven that they can fail in some cases (eg with different character sets or with certain values ).MySQLi
It has procedural mode and object oriented mode andPDO
it is object oriented.And now a warning that I put in the comments: if the functions are used
MySQLi
orPDO
in the same way that they are usedmysql_*
(concatenating strings) they are exactly as dangerous. You have to use the tools correctly (prepared statements) to be safe. Considering that the code is more secure simply for usingMySQLi
orPDO
is a serious error.Summarizing: The great advantage of
mysqli_*
andPDO
is that you can use prepared or parameterized statements . Thanks to these, complexity and responsibility are transferred to the database itself... but it is still the responsibility of the developer to use these tools correctly.As a complement to @AlvaroMontoro's answer, I would like to show the two connection possibilities offered by PHP based on the API
mysqli
and the PDO class.Any of them is valid at the time of having to migrate from
mysql_
(mandatory as of PHP 7).1. mysqli
It has two styles of code. The object- oriented style , which is clearer and more modern, and the procedural style , which is longer, more verbose, thus producing code that is harder to read 1 .
Either style is fine, but mixing styles is not recommended.
Let's now see how to connect using the two styles.
a. Connection to mysqli with object oriented style
mysqli is a class, so it can be used in object-oriented style. In that case it is used
new
, as in all classes. The constructor takes four parameters:By simply doing this, we will already have a connection object available to interact with the database:
Now we only have to use our object
$mysqli
where we need it.b. Connect to mysqli with procedural style
IMPORTANT : Although the procedural style is very widespread, and the PHP Manual always shows examples in this style as well (albeit ultimately), programming in this style should be avoided . It is more verbose and less clear than the object-oriented style and aside from that, some of its functions, including a
mysqli_connect
, are on a list of deprecated functions, as of PHP 5 .However, I show how to connect.
The connection is almost identical to the object-oriented style, with the same four parameters:
In the connection, the verbosity referred to above is not very noticeable. The issue is noticeable when we start using the functions, especially when we have to work with prepared queries and use the binding methods..., etc.
Now let's look at the charset issue:
Done, now we only have to use
$mysqli
.2.PDO
PDO only has object-oriented style. One of the great advantages of PDO is that with almost the same code, changing only the DSN and the connection credentials, we can go from one database manager (Oracle, SQL Server, MySQL, Postgresql...) to another without change all the internal code that queries data at different points in an application.
As indicated in (A), the connection to PDO is created with
new
. But the PDO constructor is more complete and supports several interesting parameters:So let's see how the connection is created. We will use variables for clarity.
The simplest connection that exists would be this, and it is the one shown in the PHP Manual:
As you can see, the connection is handled inside a block
try ... catch
, because any failure to invokenew PDO()
would raise an exception that would break the code. That's why you have to catch exceptions.BUT this basic code has several problems, so it should be improved. PDO is great, but it has some drawbacks that need to be worked out in setup:
echo 'Falló la conexión: ' . $e->getMessage();
Well, when invoking$e->getMessage()
, before a PDO exception, it would be revealing the connection credentials, password included. And that could be written in the error log, which is one of the favorite files of hackers... Do not be alarmed, we will solve that later.Given that, we can move on to improve our connection. The changes are as follows:
$dsn
we will indicate the charset. Unlikemysqli
, we don't have to use a separate function for this. We can pass it as one more data of the DSN.$options
with all the configurations that we want for the connection. Since PDO has methodssetAttribute
to modify its configuration, many users create the connection first and then modify it with those methods. From a performance point of view it is much better to create the connection already configured with everything we want instead of modifying it once created withsetAttributes
. That's exactly why PDO supports that fourth option parameter.catch
will avoid making internal error messages public. In any case, regarding the password, it is advisable to always use a long password of between 30 and 50 characters. So if it is revealed by mistake in some error log or on the screen, being long, it would be truncated.Improved connection:
Now we only have to use our object
$pdo
.As we recommend in (1). It is convenient to manage the connection to PDO in a class, which we will call every time we need to connect.
Links
Grades
mysql_
in which this parameter does not exist. In the old manuals, the use ofmysql_select_db()
after connect was repaired and many users continue this unnecessary practice when migrating to the new one.mysqli