Why shouldn’t we use mysql_* functions?

Asked

Viewed 12,507 times

247

A very common question is why should we stop using mysql_* functions? For example mysql_query(), or mysql_connect().

I see that many people use them, or stop using them but do not know the real reason. Why they should stop being used once they work?

  • 15

    This function has already become obsolete, being more indicated the use of mysqli_*

6 answers

254


Summary

The answer is only one: we should not use functions of the extension "mysql" because its development has been discontinued; the extension became obsolete, that is to say, code that uses these functions will not work in future versions of PHP.


Details

A user of the Stack Overflow site wrote:

There is no security problem with library itself but rather with library users (there are no security issues with the library itself, but rather with library users).

The vote to discontinue (deprecate) or not, resulted in 25 votes in favour and 12 votes against. It was a good majority, but it was not a unanimous decision. See the official source.

In the same font, another developer wrote:

The Extension is not Broken. The problem is the bad Usage. It can be used Safely, and good Developers have been Doing so for ages (The extension has no defects. The problem is misuse. It can be used safely, and good developers have been doing it for years).

The citation below, also from the same official document, points to the real reasons to discontinue the extension:

ext/mysql is hard to maintain code. It is not Getting new Features. Keeping it up to date for Working with new versions of libmysql or mysqlnd versions is work, we probably could Spend that time Better.

(The mysql extension contains hard-to-maintain code. It is not receiving new "Features". Keep it updated to work with new versions of libmysqld and mysqlnd is laborious; we could be using better this time.)

In other words: "So I have a hard time keeping this old code?" - as far as new versions of Mysql (and their "client libs") are released, the extension needs to be updated...

...and, in addition, extensions already exist mysqli (more modern) and the alternative PDO... So... why keep going? Let’s discontinue? Then, 25 voted "yes", 12 voted "no", and the decision was made.


Personal Comment

When I started with PHP there was no PDO extension. I used the "mysql" extension, like most, until I worked on a project using "mysqli" and became aware of the advantages, and always preferred the same since then.

Soon after, I wrote a couple of classes that extend the original extension mysqli, and I use them today in pure PHP, that is, when I’m not using a framework as the Laravel, with its "Fluent" and its "Eloquent", the Symfony with "Doctrine", etc...

For me, accustomed even to the little details of preparing "LIKE" clauses to attack-proof, the "black box" of these "Prepared statements", "Active Record" or "ORM" was viewed with suspicion. That phase passed, and today I’m a fan of the Eloquent that comes with the Laravel 4...


Addendum

After publishing the entire answer above, I felt the need to stress the importance of the factor "not Getting new Features" in the decision to discontinue the extension. That is to say, although it is the fastest (best performance) in the execution of simple tasks, it lacks the full range of functionalities that the alternative extensions (mysqli and PDO) contain (transactions, SSL support, etc.) - point that was highlighted in other responses here.

  • 19

    Besides being the best answer, still translates deprecated right, +1

  • 2

    De http://en.wikipedia.org/wiki/Deprecation: "In mainstream English, the infinitive 'to deprecate' Means, Simply, 'to strongly disapprove of (Something)'. Thus, for a standard Document to state that a Feature is deprecated is merely a Recommendation Against using it".

  • 1

    Starting with PHP 5.6 the use of the mysql_* functions returns a deprecated function error. So there is no more way to use it from then on.

  • 1

    @touchmx fix: it is possible to use them yes on 5.6 simply by turning off the deprecated errors.

  • When I read in your reply 12 voted "no" (pra 25) I was scared, it is a considerable amount of collaborators, so I went to read the link https://wiki.php.net/rfc/mysql_deprecation#why_not_other_options - basically the arguments were about the annoying messages, software outside stop working (or malfunction due to possible warning of deprecated) and tutorials that still teach the use of mysql_. Fortunately "progress" prevailed :) +1

  • 5

    Just to point out: PHP 7 no longer has the driver mysql_. It was simply removed. + 1 I loved the answer!

  • Best answer.

Show 2 more comments

122

Because the use of functions mysql_* was frowned upon (deprecated) from PHP 5.5 and, according to the documentation, functions will be removed in future versions of PHP, causing the programs that use them to stop working in those versions.

Limitations of functions mysql_*

Unlike the functions of mysqli_*, the functions mysql_* do not support

  • asynchronous, non-blocking queries
  • parameterized consultations
  • stored procedures
  • transactions

Lack of support for parameterized queries means that when using functions mysql_* without taking certain precautions, your program is vulnerable to SQL injection attacks, compromising data security and integrity. Example:

// consulta vulnerável a injeção de SQL
$query = mysql_query("SELECT * FROM alunos WHERE id = $id")

// consulta parametrizada, variável $id é higienizada
$query = mysqli_query("SELECT * FROM alunos WHERE id = ?")
$query->bind_param("i", $id);

Alternatives

Alternatively, it is recommended to use functions mysqli_*, beyond the already mentioned limitations of the functions mysql_*.

Another option is to use PDO (PHP Data Objects) which provides an abstraction layer of data access, which means that regardless of which database you are using, you can use the same functions to send queries and fetch data.

History

To learn the story behind the disapproval of functions mysql_*, see the reply from J.Bruni.

  • 6

    +1 by mentioning the PDO as an alternative.

  • 18

    -1 Although the answer was accepted, the first statement is wrong: "when using functions mysql_*, your program is subject to SQL injection attacks". This is simply not true. It is the misuse of these functions that can create vulnerabilities, not the functions themselves. The second paragraph is also incorrect: "deprecated" does not mean "frowned upon" and the main reason was not safety, but the work on keeping the code up to date (unnecessary, because there are two more modern replacement options: mysqli and PDO). And it is not "possible" that they will be removed - it is right who will be.

  • 2

    I advise you to see this answer by @J.Bruni.

  • 3

    Actually, the lack of security is mostly in the hands of the poorly informed developer who puts variables directly in the query, without even worrying about data entry and output. What can also be done wrong in mysqli.

62

We should no longer use mysql_* for the following reason:

INSERT INTO usuarios SET nome='$nome';

The value is passed directly into the sql clause, this leaves the application vulnerable to sql Injection attacks. With Pdo:

$con->prepare("INSERT INTO usuarios SET nome=?");
$con->bindParam(1, $nome);
$con->execute();

When using Pdo the query is prepared, passing through a layer of protection, layer that mysql_* does not have, at the time of replacing the values, this layer of protection goes into action checking if the command is similar to sql Injection.

  • 6

    The error can also be persisted by the user on mysqli or PDO.

  • 1

    The difference is that the first version does not offer this advantage, however, the developer can create a treatment within a method to receive only reliable data.

  • 3

    With mysqli you can use $mysqli->query('INSERT INTO usuarios SET nome=\'$nome\''); and remain equally vulnerable.

54

For a simple reason is enough for many:

This Mysql extension has been discontinued by the PHP development team. But then you ask me, why was it discontinued since it was simple and worked cool? I tell you what: As PHP is increasingly object-oriented and this Mysql extension was created for structured PHP, it started to show flaws in the object-oriented environment. Does not support Stored Procedure. Does not support transactions. Only works with Mysql up to 5.0. ... Among others. See a comparison here

Then a code with mysql methods will have a deficit in the future to support new implementations and to fix future bugs. Use PHP PDO.

  • 2

    See documentation: http://www.php.net/manual/en/intro.mysql.php

48

We see that we have many answers about this, but in short, anything that the latest version in PHP (or any language) goes into the obsolete state, we should change, try to adapt the code and if possible use newer features that may become standard in the future and PDO is an example. Unfortunately there are programmers who ignore this and will only learn and make the corrections when some migration to a server or something like that starts to accuse the error. Anyway, change needed, sooner or later would need to be done and fortunately from here a time use PDO or Mysqli will be like making a mysql_*

42

Guys, for educational purposes only, I’m posting a table containing some important information about mysql, mysqli and PDO.

inserir a descrição da imagem aqui

  • 1

    I made a small translation in the table, can be seen here, if there’s anything wrong let me know. Alias, +1 for all other answers

  • 2

    I hope to have helped with this table because it shows objectively and briefly the advantages and disadvantages of them.

  • 1

    Have you seen the translation? Could put in your answer as an alternative, Table translated into PT-BR and the external image link. I believe it would be even more useful

  • 1

    @Victorgomes Your first comment link is broken. Is there an updated link? If it doesn’t exist, please delete the comment.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.