Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


Instead, you should learn of prepared statements

NEW! - ext/mysql is now officially deprecated as of PHP 5.5!

They are no longer maintained, and are officially deprecated

"Deprecation process" is scaremongering (and it's not like php-dev has actual processes anyway). The use of mysql_* functions has been discouraged. That's not news however. That's a PHP4.4 era development. An actual selling point is ease of use, and the obvious security benefit as by-product. Which I think is more likely to convince newbies than the hypothetical function drop, or the link-ladden 08/15 comment (more likely to overcharge our typical php.net/manual eschewers).

@Kamil: That's true, but it's not really a reason why you shouldn't use it. The reason not to use it is because it's ancient, insecure, etc. :)

@Madara Uchiha i completely agree with that. I just added some background information.

@Mario -- the PHP devs do have a process, and they've just voted in favour of formally deprecating ext/mysql as of 5.5. It's no longer a hypothetical issue.

First, let's begin with the standard comment we give everyone:

Let's go through this, sentence by sentence, and explain:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

There are better, more robust and well built alternatives, PDO - PHP Database Object, which offers a complete OOP approach to database interaction, and MySQLi, which is a MySQL specific improvement.

There is one more thing: i think that function still exists in PHP for only one reason - compatibility with old, outdated but still running CMS, e-commerce, bulletin board systems etc. Finally it will be removed and you will have to rewrite your application...

This means that the PHP community is gradually dropping support for these very old functions. They are likely to not exist in a future (recent) version of PHP! Continued use of these functions may break your code in the (not so) far future.

When you go on any mysql function manual page, you see a red box, explaining it should not be used anymore.

mysql_* extension does not support prepared statements, which is (among other things) a very effective countermeasure against SQL Injection. It fixed a very serious vulnerability in MySQL dependent applications which allows attackers to gain access to your script and perform any possible query on your database.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


Instead, you should learn of prepared statements

NEW! - ext/mysql is now officially deprecated as of PHP 5.5!

They are no longer maintained, and are officially deprecated

"Deprecation process" is scaremongering (and it's not like php-dev has actual processes anyway). The use of mysql_* functions has been discouraged. That's not news however. That's a PHP4.4 era development. An actual selling point is ease of use, and the obvious security benefit as by-product. Which I think is more likely to convince newbies than the hypothetical function drop, or the link-ladden 08/15 comment (more likely to overcharge our typical php.net/manual eschewers).

@Kamil: That's true, but it's not really a reason why you shouldn't use it. The reason not to use it is because it's ancient, insecure, etc. :)

@Madara Uchiha i completely agree with that. I just added some background information.

@Mario -- the PHP devs do have a process, and they've just voted in favour of formally deprecating ext/mysql as of 5.5. It's no longer a hypothetical issue.

First, let's begin with the standard comment we give everyone:

Let's go through this, sentence by sentence, and explain:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

There are better, more robust and well built alternatives, PDO - PHP Database Object, which offers a complete OOP approach to database interaction, and MySQLi, which is a MySQL specific improvement.

There is one more thing: i think that function still exists in PHP for only one reason - compatibility with old, outdated but still running CMS, e-commerce, bulletin board systems etc. Finally it will be removed and you will have to rewrite your application...

This means that the PHP community is gradually dropping support for these very old functions. They are likely to not exist in a future (recent) version of PHP! Continued use of these functions may break your code in the (not so) far future.

When you go on any mysql function manual page, you see a red box, explaining it should not be used anymore.

mysql_* extension does not support prepared statements, which is (among other things) a very effective countermeasure against SQL Injection. It fixed a very serious vulnerability in MySQL dependent applications which allows attackers to gain access to your script and perform any possible query on your database.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


Instead, you should learn of prepared statements

NEW! - ext/mysql is now officially deprecated as of PHP 5.5!

They are no longer maintained, and are officially deprecated

"Deprecation process" is scaremongering (and it's not like php-dev has actual processes anyway). The use of mysql_* functions has been discouraged. That's not news however. That's a PHP4.4 era development. An actual selling point is ease of use, and the obvious security benefit as by-product. Which I think is more likely to convince newbies than the hypothetical function drop, or the link-ladden 08/15 comment (more likely to overcharge our typical php.net/manual eschewers).

@Kamil: That's true, but it's not really a reason why you shouldn't use it. The reason not to use it is because it's ancient, insecure, etc. :)

@Madara Uchiha i completely agree with that. I just added some background information.

@Mario -- the PHP devs do have a process, and they've just voted in favour of formally deprecating ext/mysql as of 5.5. It's no longer a hypothetical issue.

First, let's begin with the standard comment we give everyone:

Let's go through this, sentence by sentence, and explain:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

There are better, more robust and well built alternatives, PDO - PHP Database Object, which offers a complete OOP approach to database interaction, and MySQLi, which is a MySQL specific improvement.

There is one more thing: i think that function still exists in PHP for only one reason - compatibility with old, outdated but still running CMS, e-commerce, bulletin board systems etc. Finally it will be removed and you will have to rewrite your application...

This means that the PHP community is gradually dropping support for these very old functions. They are likely to not exist in a future (recent) version of PHP! Continued use of these functions may break your code in the (not so) far future.

When you go on any mysql function manual page, you see a red box, explaining it should not be used anymore.

mysql_* extension does not support prepared statements, which is (among other things) a very effective countermeasure against SQL Injection. It fixed a very serious vulnerability in MySQL dependent applications which allows attackers to gain access to your script and perform any possible query on your database.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


Instead, you should learn of prepared statements

NEW! - ext/mysql is now officially deprecated as of PHP 5.5!

They are no longer maintained, and are officially deprecated

"Deprecation process" is scaremongering (and it's not like php-dev has actual processes anyway). The use of mysql_* functions has been discouraged. That's not news however. That's a PHP4.4 era development. An actual selling point is ease of use, and the obvious security benefit as by-product. Which I think is more likely to convince newbies than the hypothetical function drop, or the link-ladden 08/15 comment (more likely to overcharge our typical php.net/manual eschewers).

@Kamil: That's true, but it's not really a reason why you shouldn't use it. The reason not to use it is because it's ancient, insecure, etc. :)

@Madara Uchiha i completely agree with that. I just added some background information.

@Mario -- the PHP devs do have a process, and they've just voted in favour of formally deprecating ext/mysql as of 5.5. It's no longer a hypothetical issue.

First, let's begin with the standard comment we give everyone:

Let's go through this, sentence by sentence, and explain:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

There are better, more robust and well built alternatives, PDO - PHP Database Object, which offers a complete OOP approach to database interaction, and MySQLi, which is a MySQL specific improvement.

There is one more thing: i think that function still exists in PHP for only one reason - compatibility with old, outdated but still running CMS, e-commerce, bulletin board systems etc. Finally it will be removed and you will have to rewrite your application...

This means that the PHP community is gradually dropping support for these very old functions. They are likely to not exist in a future (recent) version of PHP! Continued use of these functions may break your code in the (not so) far future.

When you go on any mysql function manual page, you see a red box, explaining it should not be used anymore.

mysql_* extension does not support prepared statements, which is (among other things) a very effective countermeasure against SQL Injection. It fixed a very serious vulnerability in MySQL dependent applications which allows attackers to gain access to your script and perform any possible query on your database.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


$stmt->bindParam(':bla', $bla);
$stmt->fetch(PDO::FETCH_ASSOC)
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 
              'username', 
              'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);
$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(chr(0xbf) . chr(0x27) . " OR 1=1 /*"));
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));
<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;
<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();
<?php
$result = mysql_query('SELECT * from table') or die(mysql_error());

$num_rows = mysql_num_rows($result);

while($row = mysql_fetch_assoc($result)) {
    echo $row['field1'];
}
<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);
<?php
$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->bindValue(2, $add, PDO::PARAM_STR);
$stmt->execute();
<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}
DELETE
E_DEPRECATED
E_DEPRECATED:
INSERT
INSERT INTO PRODUCT (name, price) VALUES (?, ?)
MySQLi
PDO
PDOStatement
SELECT
UPDATE
bindParam(parameter,variable,data_type,length,driver_options)
class person {
    public $name;
    public $add;
    function __construct($a,$b) {
        $this->name = $a;
        $this->add = $b;
    }

}
$demo = new person('john','29 bla district');
$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");
$stmt->execute((array)$demo);
error_reporting
error_reporting = E_ALL ^ E_DEPRECATED
function data_fun($db) {
    $stmt = $db->query("SELECT * FROM table");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

//Then later
try {
    data_fun($db);
}
catch(PDOException $ex) {
    //Here you can handle error and show message/perform action you want.
}
mysql_*
try {
    //Connect as appropriate as above
    $db->query('hi'); //Invalid query!
} 
catch (PDOException $ex) {
    echo "An Error occured!"; //User friendly message/message you want to show to user
    some_logging_function($ex->getMessage());
}

Getting the Last Inserted ID

  • Doesn't support all of the functionality in MySQL 5.1
  • Doesn't support prepared statements or parameterized queries
  • Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and 1.00 for the second parameter.
  • PDO::ERRMODE_EXCEPTION: Throw exceptions. It represents an error raised by PDO. You should not throw a PDOException from your own code. See Exceptions for more information about exceptions in PHP. It acts very much like or die(mysql_error());, when it isn't caught. But unlike or die(), the PDOException can be caught and handled gracefully if you choose to do so.
  • PDO::ERRMODE_SILENT. It's just setting error codes and acts pretty much the same as mysql_* where you must check each result and then look at $db->errorInfo(); to get the error details.
  • PDO::ERRMODE_WARNING Raise E_WARNING. (Run-time warnings (non-fatal errors). Execution of the script is not halted.)
  • PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set
  • PDOStatement::fetchAll() - Returns an array containing all of the result set rows
  • PDOStatement::fetchColumn() - Returns a single column from the next row of a result set
  • PDOStatement::fetchObject() - Fetches the next row and returns it as an object.
  • PDOStatement::setFetchMode() - Set the default fetch mode for this statement
  • Removed as of PHP 7

@Amine, No, it is not! :] While NullPoite really did a great job of writing it, this is most certainly isn't a good reading, because it is way to long. I'm pretty sure, that 8 of 10 visitors will simply skip it. And you also have explanation, why this answer isn't top voted. A tl;dr part in the beginning would be a good idea, I think.

@NullPoite As I said, your answer is great (no doubt about that!) and 405 score does reflect this. However, it is way to long. I only expressed my personal thinking and how I do understand, why other answer has more point than yours. Both answers are great, yours in only quite very long! :]

A common practice when using mysql_* is:

A. PDO PHP Data Objects is a database access layer providing a uniform method of access to multiple databases.

Also, you can handle by or die() or we can say like mysql_*, but it will be really varied. You can hide the dangerous error messages in production by turning display_errors off and just reading your error log.

And a better way is PDO, and I am now writing a simple PDO tutorial.

And in pdo, this same thing can be done by:

And the later PHP developer team has taken the decision to generate E_DEPRECATED errors when users connect to MySQL, whether through mysql_connect(), mysql_pconnect() or the implicit connection functionality built into ext/mysql.

And you can wrap it in try-catch, like below:

Another nice feature for OOP friends is that named placeholders have the ability to insert objects directly into your database, assuming the properties match the named fields. For example:

Below is an example of how you can do it:

Error handling is much easier in PDO than mysql_*.

Here I think you are familiar with all except DSN; this is new in PDO. A DSN is basically a string of options that tell PDO which driver to use, and connection details. For further reference, check PDO MySQL DSN.

However PDO and/or MySQLi are not completely safe. Check the answer Are PDO prepared statements sufficient to prevent SQL injection? by ircmaxell. Also, I am quoting some part from his answer:

I will later explain why prepare emulation should be turned off. To find reason please check this post.

If there is any connection error, it will throw a PDOException object that can be cached to handle Exception further.

In PDO Data, it is obtained via the ->fetch(), a method of your statement handle. Before calling fetch, the best approach would be telling PDO how youd like the data to be fetched. In the below section I am explaining this.

In the above query PDO::exec execute an SQL statement and returns the number of affected rows.

In the above, I have been using fetch(). You can also use:

In the above, you can see those ? instead of a name like in a name place holder. Now in the first example, we assign variables to the various placeholders ($stmt->bindValue(1, $name, PDO::PARAM_STR);). Then, we assign values to those placeholders and execute the statement. In the second example, the first array element goes to the first ? and the second to the second ?.

Instead of using mysql_num_rows to get the number of returned rows, you can get a PDOStatement and do rowCount(), like:

It is only usable if you are using an old version of MySQL which I do not recommended.

Just out of curiosity, is there some reason that MySQL/PHP developers couldn't have extended the MySQL library, updating the code behind existing functions and adding new function (including OO), all the while maintaining upward compatibility? Was that simply impossible to do cleanly, or they didn't want to bother? There's something to be said for maintaining upward compatibility, so existing applications don't break.

Lack of support for prepared statements is particularly important as they provide a clearer, less error prone method of escaping and quoting external data than manually escaping it with a separate function call.

Moving away from ext/mysql is not only about security, but also about having access to all the features of the MySQL database.

NOTE: In unnamed placeholders we must take care of the proper order of the elements in the array that we are passing to the PDOStatement::execute() method.

Note that this will also hide other deprecation warnings, which, however, may be for things other than MySQL. (from PHP manual)

Note the use of PDO::FETCH_ASSOC in the fetch() and fetchAll() code above. This tells PDO to return the rows as an associative array with the field names as keys. There are many other fetch modes too which I will explain one by one.

Note: If you are using the method like below (query()), this method returns a PDOStatement object. So if you want to fetch the result, use it like above.

Note: you can also use charset=UTF-8, but sometimes it causes an error, so it's better to use utf8.

Now in PDO, you can do this like:

Now, after reading all the things above, you are probably thinking: what the heck is that when I just want to start leaning simple SELECT, INSERT, UPDATE, or DELETE statements? Don't worry, here we go:

OR die() is not a good way to handle the error since we can not handle the thing in die. It will just end the script abruptly and then echo the error to the screen which you usually do NOT want to show to your end users, and let bloody hackers discover your schema. Alternately, the return values of mysql_* functions can often be used in conjunction with mysql_error() to handle errors.

PDO offers a better solution: exceptions. Anything we do with PDO should be wrapped in a try-catch block. We can force PDO into one of three error modes by setting the error mode attribute. Three error handling modes are below.

PHP offers three different APIs to connect to MySQL. These are the mysql(removed as of PHP 7), mysqli, and PDO extensions.

Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled ? below):

Q. So now, what are named placeholders and how do I use them?A. Named placeholders. Use descriptive names preceded by a colon, instead of question marks. We don't care about position/order of value in name place holder:

Q. So now, what are unnamed placeholders and how do I use them?A. Let's have an example:

Q. What is a prepared statement and why do I need them?A. A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending only the data to the server.

So what you are doing in mysql_* is:

The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.

The above method is only useful when you are not using variable in query. But when you need to use a variable in a query, do not ever ever try like the above and there for prepared statement or parameterized statement is.

The article PDO vs. MySQLi: Which Should You Use? by Dejan Marjanovic will help you to choose.

The mysql_* functions used to be very popular, but their use is not encouraged anymore. The documentation team is discussing the database security situation, and educating users to move away from the commonly used ext/mysql extension is part of this (check php.internals: deprecating ext/mysql).

The other is to turn off prepare emulation which is enabled in the MySQL driver by default, but prepare emulation should be turned off to use PDO safely.

The question was "Why shouldnt I use mysql_* functions in PHP". This answer, while impressive and full of helpful information, goes WAY out of scope and like @trejder says - 8 out of 10 people are going to miss out on that information simply because they don't have 4 hours to spend trying to work through it. This would be far more valuable broken up and used as answers to several, more precise, questions.

The typical workflow of using a prepared statement is as follows (quoted from Wikipedia three 3 point):

What the good read above should propably mention: prepared statement take away any meaningful use of the IN (...) construct.

What we are doing in mysql_* function is:

When you go on any mysql_* function manual page, you see a red box, explaining it should not be used anymore.

With PDO: All you need to do is create a new PDO object. The constructor accepts parameters for specifying the database source PDO's constructor mostly takes four parameters which are DSN (data source name) and optionally username, password.

With mysql_* function or we can say it the old way (deprecated in PHP 5.5 and above)

Yes, we can also set some attributes after PDO construction with the setAttribute method:

You can also bind using an execute array as well:

You can also pass in several driver options as an array to the fourth parameter. I recommend passing the parameter which puts PDO into exception mode. Because some PDO drivers don't support native prepared statements, so PDO performs emulation of the prepare. It also lets you manually enable this emulation. To use the native server-side prepared statements, you should explicitly set it false.

You can use a prepared statement by including placeholders in your SQL. There are basically three ones without placeholders (don't try this with variable its above one), one with unnamed placeholders, and one with named placeholders.

You do not have to handle with try-catch right now. You can catch it at any time appropriate, but I strongly recommend you to use try-catch. Also it may make more sense to catch it at outside the function that calls the PDO stuff:

and

ext/mysql was built for MySQL 3.23 and only got very few additions since then while mostly keeping compatibility with this old version which makes the code a bit harder to maintain. Missing features that is not supported by ext/mysql include: (from PHP manual).

ext/mysql was officially deprecated as of PHP 5.5 and has been removed as of PHP 7.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


$stmt->bindParam(':bla', $bla);
$stmt->fetch(PDO::FETCH_ASSOC)
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 
              'username', 
              'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);
$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(chr(0xbf) . chr(0x27) . " OR 1=1 /*"));
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));
<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;
<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();
<?php
$result = mysql_query('SELECT * from table') or die(mysql_error());

$num_rows = mysql_num_rows($result);

while($row = mysql_fetch_assoc($result)) {
    echo $row['field1'];
}
<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);
<?php
$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->bindValue(2, $add, PDO::PARAM_STR);
$stmt->execute();
<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}
DELETE
E_DEPRECATED
E_DEPRECATED:
INSERT
INSERT INTO PRODUCT (name, price) VALUES (?, ?)
MySQLi
PDO
PDOStatement
SELECT
UPDATE
bindParam(parameter,variable,data_type,length,driver_options)
class person {
    public $name;
    public $add;
    function __construct($a,$b) {
        $this->name = $a;
        $this->add = $b;
    }

}
$demo = new person('john','29 bla district');
$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");
$stmt->execute((array)$demo);
error_reporting
error_reporting = E_ALL ^ E_DEPRECATED
function data_fun($db) {
    $stmt = $db->query("SELECT * FROM table");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

//Then later
try {
    data_fun($db);
}
catch(PDOException $ex) {
    //Here you can handle error and show message/perform action you want.
}
mysql_*
try {
    //Connect as appropriate as above
    $db->query('hi'); //Invalid query!
} 
catch (PDOException $ex) {
    echo "An Error occured!"; //User friendly message/message you want to show to user
    some_logging_function($ex->getMessage());
}

Getting the Last Inserted ID

  • Doesn't support all of the functionality in MySQL 5.1
  • Doesn't support prepared statements or parameterized queries
  • Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and 1.00 for the second parameter.
  • PDO::ERRMODE_EXCEPTION: Throw exceptions. It represents an error raised by PDO. You should not throw a PDOException from your own code. See Exceptions for more information about exceptions in PHP. It acts very much like or die(mysql_error());, when it isn't caught. But unlike or die(), the PDOException can be caught and handled gracefully if you choose to do so.
  • PDO::ERRMODE_SILENT. It's just setting error codes and acts pretty much the same as mysql_* where you must check each result and then look at $db->errorInfo(); to get the error details.
  • PDO::ERRMODE_WARNING Raise E_WARNING. (Run-time warnings (non-fatal errors). Execution of the script is not halted.)
  • PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set
  • PDOStatement::fetchAll() - Returns an array containing all of the result set rows
  • PDOStatement::fetchColumn() - Returns a single column from the next row of a result set
  • PDOStatement::fetchObject() - Fetches the next row and returns it as an object.
  • PDOStatement::setFetchMode() - Set the default fetch mode for this statement
  • Removed as of PHP 7

@Amine, No, it is not! :] While NullPoite really did a great job of writing it, this is most certainly isn't a good reading, because it is way to long. I'm pretty sure, that 8 of 10 visitors will simply skip it. And you also have explanation, why this answer isn't top voted. A tl;dr part in the beginning would be a good idea, I think.

@NullPoite As I said, your answer is great (no doubt about that!) and 405 score does reflect this. However, it is way to long. I only expressed my personal thinking and how I do understand, why other answer has more point than yours. Both answers are great, yours in only quite very long! :]

A common practice when using mysql_* is:

A. PDO PHP Data Objects is a database access layer providing a uniform method of access to multiple databases.

Also, you can handle by or die() or we can say like mysql_*, but it will be really varied. You can hide the dangerous error messages in production by turning display_errors off and just reading your error log.

And a better way is PDO, and I am now writing a simple PDO tutorial.

And in pdo, this same thing can be done by:

And the later PHP developer team has taken the decision to generate E_DEPRECATED errors when users connect to MySQL, whether through mysql_connect(), mysql_pconnect() or the implicit connection functionality built into ext/mysql.

And you can wrap it in try-catch, like below:

Another nice feature for OOP friends is that named placeholders have the ability to insert objects directly into your database, assuming the properties match the named fields. For example:

Below is an example of how you can do it:

Error handling is much easier in PDO than mysql_*.

Here I think you are familiar with all except DSN; this is new in PDO. A DSN is basically a string of options that tell PDO which driver to use, and connection details. For further reference, check PDO MySQL DSN.

However PDO and/or MySQLi are not completely safe. Check the answer Are PDO prepared statements sufficient to prevent SQL injection? by ircmaxell. Also, I am quoting some part from his answer:

I will later explain why prepare emulation should be turned off. To find reason please check this post.

If there is any connection error, it will throw a PDOException object that can be cached to handle Exception further.

In PDO Data, it is obtained via the ->fetch(), a method of your statement handle. Before calling fetch, the best approach would be telling PDO how youd like the data to be fetched. In the below section I am explaining this.

In the above query PDO::exec execute an SQL statement and returns the number of affected rows.

In the above, I have been using fetch(). You can also use:

In the above, you can see those ? instead of a name like in a name place holder. Now in the first example, we assign variables to the various placeholders ($stmt->bindValue(1, $name, PDO::PARAM_STR);). Then, we assign values to those placeholders and execute the statement. In the second example, the first array element goes to the first ? and the second to the second ?.

Instead of using mysql_num_rows to get the number of returned rows, you can get a PDOStatement and do rowCount(), like:

It is only usable if you are using an old version of MySQL which I do not recommended.

Just out of curiosity, is there some reason that MySQL/PHP developers couldn't have extended the MySQL library, updating the code behind existing functions and adding new function (including OO), all the while maintaining upward compatibility? Was that simply impossible to do cleanly, or they didn't want to bother? There's something to be said for maintaining upward compatibility, so existing applications don't break.

Lack of support for prepared statements is particularly important as they provide a clearer, less error prone method of escaping and quoting external data than manually escaping it with a separate function call.

Moving away from ext/mysql is not only about security, but also about having access to all the features of the MySQL database.

NOTE: In unnamed placeholders we must take care of the proper order of the elements in the array that we are passing to the PDOStatement::execute() method.

Note that this will also hide other deprecation warnings, which, however, may be for things other than MySQL. (from PHP manual)

Note the use of PDO::FETCH_ASSOC in the fetch() and fetchAll() code above. This tells PDO to return the rows as an associative array with the field names as keys. There are many other fetch modes too which I will explain one by one.

Note: If you are using the method like below (query()), this method returns a PDOStatement object. So if you want to fetch the result, use it like above.

Note: you can also use charset=UTF-8, but sometimes it causes an error, so it's better to use utf8.

Now in PDO, you can do this like:

Now, after reading all the things above, you are probably thinking: what the heck is that when I just want to start leaning simple SELECT, INSERT, UPDATE, or DELETE statements? Don't worry, here we go:

OR die() is not a good way to handle the error since we can not handle the thing in die. It will just end the script abruptly and then echo the error to the screen which you usually do NOT want to show to your end users, and let bloody hackers discover your schema. Alternately, the return values of mysql_* functions can often be used in conjunction with mysql_error() to handle errors.

PDO offers a better solution: exceptions. Anything we do with PDO should be wrapped in a try-catch block. We can force PDO into one of three error modes by setting the error mode attribute. Three error handling modes are below.

PHP offers three different APIs to connect to MySQL. These are the mysql(removed as of PHP 7), mysqli, and PDO extensions.

Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled ? below):

Q. So now, what are named placeholders and how do I use them?A. Named placeholders. Use descriptive names preceded by a colon, instead of question marks. We don't care about position/order of value in name place holder:

Q. So now, what are unnamed placeholders and how do I use them?A. Let's have an example:

Q. What is a prepared statement and why do I need them?A. A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending only the data to the server.

So what you are doing in mysql_* is:

The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.

The above method is only useful when you are not using variable in query. But when you need to use a variable in a query, do not ever ever try like the above and there for prepared statement or parameterized statement is.

The article PDO vs. MySQLi: Which Should You Use? by Dejan Marjanovic will help you to choose.

The mysql_* functions used to be very popular, but their use is not encouraged anymore. The documentation team is discussing the database security situation, and educating users to move away from the commonly used ext/mysql extension is part of this (check php.internals: deprecating ext/mysql).

The other is to turn off prepare emulation which is enabled in the MySQL driver by default, but prepare emulation should be turned off to use PDO safely.

The question was "Why shouldnt I use mysql_* functions in PHP". This answer, while impressive and full of helpful information, goes WAY out of scope and like @trejder says - 8 out of 10 people are going to miss out on that information simply because they don't have 4 hours to spend trying to work through it. This would be far more valuable broken up and used as answers to several, more precise, questions.

The typical workflow of using a prepared statement is as follows (quoted from Wikipedia three 3 point):

What the good read above should propably mention: prepared statement take away any meaningful use of the IN (...) construct.

What we are doing in mysql_* function is:

When you go on any mysql_* function manual page, you see a red box, explaining it should not be used anymore.

With PDO: All you need to do is create a new PDO object. The constructor accepts parameters for specifying the database source PDO's constructor mostly takes four parameters which are DSN (data source name) and optionally username, password.

With mysql_* function or we can say it the old way (deprecated in PHP 5.5 and above)

Yes, we can also set some attributes after PDO construction with the setAttribute method:

You can also bind using an execute array as well:

You can also pass in several driver options as an array to the fourth parameter. I recommend passing the parameter which puts PDO into exception mode. Because some PDO drivers don't support native prepared statements, so PDO performs emulation of the prepare. It also lets you manually enable this emulation. To use the native server-side prepared statements, you should explicitly set it false.

You can use a prepared statement by including placeholders in your SQL. There are basically three ones without placeholders (don't try this with variable its above one), one with unnamed placeholders, and one with named placeholders.

You do not have to handle with try-catch right now. You can catch it at any time appropriate, but I strongly recommend you to use try-catch. Also it may make more sense to catch it at outside the function that calls the PDO stuff:

and

ext/mysql was built for MySQL 3.23 and only got very few additions since then while mostly keeping compatibility with this old version which makes the code a bit harder to maintain. Missing features that is not supported by ext/mysql include: (from PHP manual).

ext/mysql was officially deprecated as of PHP 5.5 and has been removed as of PHP 7.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


$stmt->bindParam(':bla', $bla);
$stmt->fetch(PDO::FETCH_ASSOC)
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 
              'username', 
              'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);
$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(chr(0xbf) . chr(0x27) . " OR 1=1 /*"));
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));
<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;
<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();
<?php
$result = mysql_query('SELECT * from table') or die(mysql_error());

$num_rows = mysql_num_rows($result);

while($row = mysql_fetch_assoc($result)) {
    echo $row['field1'];
}
<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);
<?php
$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->bindValue(2, $add, PDO::PARAM_STR);
$stmt->execute();
<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}
DELETE
E_DEPRECATED
E_DEPRECATED:
INSERT
INSERT INTO PRODUCT (name, price) VALUES (?, ?)
MySQLi
PDO
PDOStatement
SELECT
UPDATE
bindParam(parameter,variable,data_type,length,driver_options)
class person {
    public $name;
    public $add;
    function __construct($a,$b) {
        $this->name = $a;
        $this->add = $b;
    }

}
$demo = new person('john','29 bla district');
$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");
$stmt->execute((array)$demo);
error_reporting
error_reporting = E_ALL ^ E_DEPRECATED
function data_fun($db) {
    $stmt = $db->query("SELECT * FROM table");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

//Then later
try {
    data_fun($db);
}
catch(PDOException $ex) {
    //Here you can handle error and show message/perform action you want.
}
mysql_*
try {
    //Connect as appropriate as above
    $db->query('hi'); //Invalid query!
} 
catch (PDOException $ex) {
    echo "An Error occured!"; //User friendly message/message you want to show to user
    some_logging_function($ex->getMessage());
}

Getting the Last Inserted ID

  • Doesn't support all of the functionality in MySQL 5.1
  • Doesn't support prepared statements or parameterized queries
  • Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and 1.00 for the second parameter.
  • PDO::ERRMODE_EXCEPTION: Throw exceptions. It represents an error raised by PDO. You should not throw a PDOException from your own code. See Exceptions for more information about exceptions in PHP. It acts very much like or die(mysql_error());, when it isn't caught. But unlike or die(), the PDOException can be caught and handled gracefully if you choose to do so.
  • PDO::ERRMODE_SILENT. It's just setting error codes and acts pretty much the same as mysql_* where you must check each result and then look at $db->errorInfo(); to get the error details.
  • PDO::ERRMODE_WARNING Raise E_WARNING. (Run-time warnings (non-fatal errors). Execution of the script is not halted.)
  • PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set
  • PDOStatement::fetchAll() - Returns an array containing all of the result set rows
  • PDOStatement::fetchColumn() - Returns a single column from the next row of a result set
  • PDOStatement::fetchObject() - Fetches the next row and returns it as an object.
  • PDOStatement::setFetchMode() - Set the default fetch mode for this statement
  • Removed as of PHP 7

@Amine, No, it is not! :] While NullPoite really did a great job of writing it, this is most certainly isn't a good reading, because it is way to long. I'm pretty sure, that 8 of 10 visitors will simply skip it. And you also have explanation, why this answer isn't top voted. A tl;dr part in the beginning would be a good idea, I think.

@NullPoite As I said, your answer is great (no doubt about that!) and 405 score does reflect this. However, it is way to long. I only expressed my personal thinking and how I do understand, why other answer has more point than yours. Both answers are great, yours in only quite very long! :]

A common practice when using mysql_* is:

A. PDO PHP Data Objects is a database access layer providing a uniform method of access to multiple databases.

Also, you can handle by or die() or we can say like mysql_*, but it will be really varied. You can hide the dangerous error messages in production by turning display_errors off and just reading your error log.

And a better way is PDO, and I am now writing a simple PDO tutorial.

And in pdo, this same thing can be done by:

And the later PHP developer team has taken the decision to generate E_DEPRECATED errors when users connect to MySQL, whether through mysql_connect(), mysql_pconnect() or the implicit connection functionality built into ext/mysql.

And you can wrap it in try-catch, like below:

Another nice feature for OOP friends is that named placeholders have the ability to insert objects directly into your database, assuming the properties match the named fields. For example:

Below is an example of how you can do it:

Error handling is much easier in PDO than mysql_*.

Here I think you are familiar with all except DSN; this is new in PDO. A DSN is basically a string of options that tell PDO which driver to use, and connection details. For further reference, check PDO MySQL DSN.

However PDO and/or MySQLi are not completely safe. Check the answer Are PDO prepared statements sufficient to prevent SQL injection? by ircmaxell. Also, I am quoting some part from his answer:

I will later explain why prepare emulation should be turned off. To find reason please check this post.

If there is any connection error, it will throw a PDOException object that can be cached to handle Exception further.

In PDO Data, it is obtained via the ->fetch(), a method of your statement handle. Before calling fetch, the best approach would be telling PDO how youd like the data to be fetched. In the below section I am explaining this.

In the above query PDO::exec execute an SQL statement and returns the number of affected rows.

In the above, I have been using fetch(). You can also use:

In the above, you can see those ? instead of a name like in a name place holder. Now in the first example, we assign variables to the various placeholders ($stmt->bindValue(1, $name, PDO::PARAM_STR);). Then, we assign values to those placeholders and execute the statement. In the second example, the first array element goes to the first ? and the second to the second ?.

Instead of using mysql_num_rows to get the number of returned rows, you can get a PDOStatement and do rowCount(), like:

It is only usable if you are using an old version of MySQL which I do not recommended.

Just out of curiosity, is there some reason that MySQL/PHP developers couldn't have extended the MySQL library, updating the code behind existing functions and adding new function (including OO), all the while maintaining upward compatibility? Was that simply impossible to do cleanly, or they didn't want to bother? There's something to be said for maintaining upward compatibility, so existing applications don't break.

Lack of support for prepared statements is particularly important as they provide a clearer, less error prone method of escaping and quoting external data than manually escaping it with a separate function call.

Moving away from ext/mysql is not only about security, but also about having access to all the features of the MySQL database.

NOTE: In unnamed placeholders we must take care of the proper order of the elements in the array that we are passing to the PDOStatement::execute() method.

Note that this will also hide other deprecation warnings, which, however, may be for things other than MySQL. (from PHP manual)

Note the use of PDO::FETCH_ASSOC in the fetch() and fetchAll() code above. This tells PDO to return the rows as an associative array with the field names as keys. There are many other fetch modes too which I will explain one by one.

Note: If you are using the method like below (query()), this method returns a PDOStatement object. So if you want to fetch the result, use it like above.

Note: you can also use charset=UTF-8, but sometimes it causes an error, so it's better to use utf8.

Now in PDO, you can do this like:

Now, after reading all the things above, you are probably thinking: what the heck is that when I just want to start leaning simple SELECT, INSERT, UPDATE, or DELETE statements? Don't worry, here we go:

OR die() is not a good way to handle the error since we can not handle the thing in die. It will just end the script abruptly and then echo the error to the screen which you usually do NOT want to show to your end users, and let bloody hackers discover your schema. Alternately, the return values of mysql_* functions can often be used in conjunction with mysql_error() to handle errors.

PDO offers a better solution: exceptions. Anything we do with PDO should be wrapped in a try-catch block. We can force PDO into one of three error modes by setting the error mode attribute. Three error handling modes are below.

PHP offers three different APIs to connect to MySQL. These are the mysql(removed as of PHP 7), mysqli, and PDO extensions.

Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled ? below):

Q. So now, what are named placeholders and how do I use them?A. Named placeholders. Use descriptive names preceded by a colon, instead of question marks. We don't care about position/order of value in name place holder:

Q. So now, what are unnamed placeholders and how do I use them?A. Let's have an example:

Q. What is a prepared statement and why do I need them?A. A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending only the data to the server.

So what you are doing in mysql_* is:

The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.

The above method is only useful when you are not using variable in query. But when you need to use a variable in a query, do not ever ever try like the above and there for prepared statement or parameterized statement is.

The article PDO vs. MySQLi: Which Should You Use? by Dejan Marjanovic will help you to choose.

The mysql_* functions used to be very popular, but their use is not encouraged anymore. The documentation team is discussing the database security situation, and educating users to move away from the commonly used ext/mysql extension is part of this (check php.internals: deprecating ext/mysql).

The other is to turn off prepare emulation which is enabled in the MySQL driver by default, but prepare emulation should be turned off to use PDO safely.

The question was "Why shouldnt I use mysql_* functions in PHP". This answer, while impressive and full of helpful information, goes WAY out of scope and like @trejder says - 8 out of 10 people are going to miss out on that information simply because they don't have 4 hours to spend trying to work through it. This would be far more valuable broken up and used as answers to several, more precise, questions.

The typical workflow of using a prepared statement is as follows (quoted from Wikipedia three 3 point):

What the good read above should propably mention: prepared statement take away any meaningful use of the IN (...) construct.

What we are doing in mysql_* function is:

When you go on any mysql_* function manual page, you see a red box, explaining it should not be used anymore.

With PDO: All you need to do is create a new PDO object. The constructor accepts parameters for specifying the database source PDO's constructor mostly takes four parameters which are DSN (data source name) and optionally username, password.

With mysql_* function or we can say it the old way (deprecated in PHP 5.5 and above)

Yes, we can also set some attributes after PDO construction with the setAttribute method:

You can also bind using an execute array as well:

You can also pass in several driver options as an array to the fourth parameter. I recommend passing the parameter which puts PDO into exception mode. Because some PDO drivers don't support native prepared statements, so PDO performs emulation of the prepare. It also lets you manually enable this emulation. To use the native server-side prepared statements, you should explicitly set it false.

You can use a prepared statement by including placeholders in your SQL. There are basically three ones without placeholders (don't try this with variable its above one), one with unnamed placeholders, and one with named placeholders.

You do not have to handle with try-catch right now. You can catch it at any time appropriate, but I strongly recommend you to use try-catch. Also it may make more sense to catch it at outside the function that calls the PDO stuff:

and

ext/mysql was built for MySQL 3.23 and only got very few additions since then while mostly keeping compatibility with this old version which makes the code a bit harder to maintain. Missing features that is not supported by ext/mysql include: (from PHP manual).

ext/mysql was officially deprecated as of PHP 5.5 and has been removed as of PHP 7.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


$result = pdo_query("SELECT * FROM tbl");
while ($row = pdo_fetch_assoc($result)) {
$result->fetchAll();
:named
_real_escape_string
connect()
fetch_array()
fetch_assoc()
foreach ($result as $row) {
function sanitize($str) {
   return trim(strip_tags(htmlentities(pdo_real_escape_string($str))));
}
insert_id()
mysql_
num_rows()
pdo_
pdo_query("INSERT INTO pages VALUES (?,?,?,?,?)", $_POST);
pdo_query("INSERT INTO users VALUES (?, ?, ?), $_POST);  $_POST = array( 'username' => 'lawl', 'password' => '123', 'is_admin' => 'true');
pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title=? OR id=? AND user<>? ORDER BY date", $title, $id, $root)
pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title='" . pdo_real_escape_string($title) . "' OR id='".
   pdo_real_escape_string($title) . "' AND user <> '" .
   pdo_real_escape_string($root) . "' ORDER BY date")
pdo_query()
query()
real_escape_string()
  • Add them as comma delimited function parameters to pdo_query().
  • Get rid of ' single quotes that previously enclosed string values/variables.
  • Place question marks ? as placeholders where the variables were before.
  • The placeholder feature is provided by the real PDO behind it.
  • Your code will work alike and still mostly look the same: include_once("pdo_mysql.php"); pdo_connect("localhost", "usrABC", "pw1234567"); pdo_select_db("test"); $result = pdo_query("SELECT title, html FROM pages"); while ($row = pdo_fetch_assoc($result)) { print "$row[title] - $row[html]"; }

@Tom Sure, albeit it's not maintained much (0.9.2 was the last), you can create a fossil account, add to the wiki or file a bug report (without registration IIRC).

@mario i'm thinking about using your pdo_* functions in a major legacy codebase. At the moment, i'm just experimenting, but already found a bug. Is there any way i can contribute to this project? Is 0.9.2 the latest version? THX!

Beware that parameter binding still isn't a magic one-stop solution against all SQL injections. It handles the most common use for data/values. But can't whitelist column name / table identifiers, help with dynamic clause construction, or just plain array value lists.

Can be replaced with just an foreach iteration:

Correct order would have been: deprecatedly stripslashes as the innermost call, then trim, afterwards strip_tags, htmlentities for output context, and only lastly the _escape_string as its application should directly preceed the SQL intersparsing.

Et voil. Your code is using PDO. Now it's time to actually utilize it.

Historic note on magic_quotes. That feature is rightly deprecated. It's often incorrectly portrayed as failed security feature however. But magic_quotes are as much a failed security feature as tennis balls have failed as nutrition source. That simply wasn't their purpose.

If there was any mention of stripslashes() in your sanitize function, it may indicate a higher level oversight.

If you want to simplify your database interaction further, mappers like Paris/Idiorm are worth a try. Just like nobody uses the bland DOM in JavaScript anymore, you don't have to babysit a raw database interface nowadays.

In particular you should fix any sanitize or clean or filterThis or clean_data functions as advertised by dated tutorials in one form or the other:

It's mostly the bound parameters which can simplify code. And with excellent tutorials (as seen above) the transition to PDO isn't overly arduous.

More importantly you can pass $_REQUEST[] variables safely behind any query. When submitted <form> fields match the database structure exactly it's even shorter:

Most glaring bug here is the lack of documentation. More significantly the order of filtering was in exactly the wrong order.

Move your variables out of the SQL string.

Often string variables aren't just interpolated into SQL, but concatenated with escaping calls in between.

Once you have converted all mysql_ calls to pdo_query with bound params, remove all redundant pdo_real_escape_string calls.

Or better yet a direct and complete array retrieval:

Remember that pdo_* still allows either or. Just don't escape a variable and bind it in the same query.

Remove the mysql_ function prefix everywhere and replace it with pdo_.

Simply include_once("pdo_mysql.php"); in each invocation script that has to interact with the database.

So much simplicity. But let's get back to some more rewriting advises and technical reasons on why you may want to get rid of mysql_ and escaping.

Thanks. I'm mainly fond of the abstract pictograms visualizing parameterized queries. But I'll probably expand more on a later second conversion from pdo_query() to real PDO:: usage.

The analytic and synthetic reasons were already mentioned. For newcomers there's a more significant incentive to stop using the dated mysql_ functions.

The original implementation in PHP2/FI introduced it explicitly with just "quotes will be automatically escaped making it easier to pass form data directly to msql queries". Notably it was accidentially safe to use with mSQL, as that supported ASCII only. Then PHP3/Zend reintroduced magic_quotes for MySQL and misdocumented it. But originally it was just a convenience feature, not intend for security.

These pdo_* wrapper functions make a coding-friendly stop-gap API. (It's pretty much what MYSQLI could have been if it wasn't for the idiosyncratic function signature shift). They also expose the real PDO at most times. Rewriting doesn't have to stop at using the new pdo_ function names. You could one by one transition each pdo_query() into a plain $pdo->prepare()->execute() call.

Unless you also introduce parameter binding or can utilize something else from the nicer API, it's a pointless switch. I hope it's portrayed simple enough to not further the discouragement to newcomers. (Education usually works better than prohibition.)

Using <pdo_mysql.php> you can switch from the old mysql_ functions with minimal effort. It adds pdo_ function wrappers which replace their mysql_ counterparts.

While it qualifies for the simplest-thing-that-could-possibly-work category, it's also still very experimental code. I just wrote it over the weekend. There's a plethora of alternatives however. Just google for PHP database abstraction and browse a little. There always have been and will be lots of excellent libraries for such tasks.

While this answer stresses that you should care about the readability advantages of dropping mysql_. There's occasionally also a performance advantage (repeated INSERTs with just differing values) due to this visible and technical data/code separation.

With ? placeholders applied you don't have to bother with that:

With bound parameters you separate SQL code and SQL-context values in your PHP code. But it doesn't get shuffled up again behind the scenes (except with PDO::EMULATE_PREPARES). Your database receives the unvaried SQL commands and 1:1 variable values.

You just need a less unwieldy API.

You may have to keep the rest of your sanitize() function for now if your database and application flow expect HTML-context-safe strings. Add a comment that it applies only HTML escaping henceforth.

pdo_query() adds very facile support for bound parameters. Converting old code is straightforward:

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


  • All of the functionality in MySQL 5.1
  • Is officially deprecated as of PHP 5.5 (released June 2013).
  • The "new" password authentication method (on by default in MySQL 5.6; required in 5.7)
  • This means that as of 31 Dec 2018 it will not exist in any supported version of PHP. Currently it only gets security updates.

@LightnessRacesinOrbit Deprecation isn't a magic bullet, it is a flag that says "We recognise this sucks so we aren't going to support it for much longer". While having better future proofing of code is a good reason to move away from the deprecated features, it isn't the only one (or even the main one). Change tools because there are better tools, not because you are forced to. (And changing tools before you are forced to means that you aren't learning the new ones just because your code has stopped working and needs fixing yesterday which is the worst time to learn new tools).

@symcbean, It surely does not support prepared statements. That's in fact the main reason why it's deprecated. Without (easy to use) prepared statements the mysql extension often falls victim to SQL injection attacks.

Deprecated alone is reason enough to avoid them. They will not be there one day, and you will not be happy if you rely on them. The rest is just a list of things that using the old extensions has kept people from learning.

Deprecation isn't the magic bullet everyone seems to think it is. PHP itself will not be there one day, yet we rely on the tools we have at our disposal today. When we have to change tools, we will.

Lack of support for prepared statements is particularly important as they provide a clearer, less error prone method of escaping and quoting external data than manually escaping it with a separate function call.

One thing that I haven't seen mentioned about the lack of prepared statements is the performance issue. Every time you issue a statement, something has to compile it so that the MySQL daemon can understand it. With this API, if you issue 200,000 of the same query in a loop, that's 200,000 times the query has to be compiled for MySQL to understand it. With prepared statements, it's compiled once, and then values are parameterized into the compiled SQL.

See the comparison of SQL extensions.

Since it is deprecated, using it makes your code less future proof.

Note
Rectangle 27 1

mysql Why shouldn't I use mysql_* functions in PHP?


$result = pdo_query("SELECT * FROM tbl");
while ($row = pdo_fetch_assoc($result)) {
$result->fetchAll();
:named
_real_escape_string
connect()
fetch_array()
fetch_assoc()
foreach ($result as $row) {
function sanitize($str) {
   return trim(strip_tags(htmlentities(pdo_real_escape_string($str))));
}
insert_id()
mysql_
num_rows()
pdo_
pdo_query("INSERT INTO pages VALUES (?,?,?,?,?)", $_POST);
pdo_query("INSERT INTO users VALUES (?, ?, ?), $_POST);  $_POST = array( 'username' => 'lawl', 'password' => '123', 'is_admin' => 'true');
pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title=? OR id=? AND user<>? ORDER BY date", $title, $id, $root)
pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title='" . pdo_real_escape_string($title) . "' OR id='".
   pdo_real_escape_string($title) . "' AND user <> '" .
   pdo_real_escape_string($root) . "' ORDER BY date")
pdo_query()
query()
real_escape_string()
  • Add them as comma delimited function parameters to pdo_query().
  • Get rid of ' single quotes that previously enclosed string values/variables.
  • Place question marks ? as placeholders where the variables were before.
  • The placeholder feature is provided by the real PDO behind it.
  • Your code will work alike and still mostly look the same: include_once("pdo_mysql.php"); pdo_connect("localhost", "usrABC", "pw1234567"); pdo_select_db("test"); $result = pdo_query("SELECT title, html FROM pages"); while ($row = pdo_fetch_assoc($result)) { print "$row[title] - $row[html]"; }

@Tom Sure, albeit it's not maintained much (0.9.2 was the last), you can create a fossil account, add to the wiki or file a bug report (without registration IIRC).

@mario i'm thinking about using your pdo_* functions in a major legacy codebase. At the moment, i'm just experimenting, but already found a bug. Is there any way i can contribute to this project? Is 0.9.2 the latest version? THX!

Beware that parameter binding still isn't a magic one-stop solution against all SQL injections. It handles the most common use for data/values. But can't whitelist column name / table identifiers, help with dynamic clause construction, or just plain array value lists.

Can be replaced with just an foreach iteration:

Correct order would have been: deprecatedly stripslashes as the innermost call, then trim, afterwards strip_tags, htmlentities for output context, and only lastly the _escape_string as its application should directly preceed the SQL intersparsing.

Et voil. Your code is using PDO. Now it's time to actually utilize it.

Historic note on magic_quotes. That feature is rightly deprecated. It's often incorrectly portrayed as failed security feature however. But magic_quotes are as much a failed security feature as tennis balls have failed as nutrition source. That simply wasn't their purpose.

If there was any mention of stripslashes() in your sanitize function, it may indicate a higher level oversight.

If you want to simplify your database interaction further, mappers like Paris/Idiorm are worth a try. Just like nobody uses the bland DOM in JavaScript anymore, you don't have to babysit a raw database interface nowadays.

In particular you should fix any sanitize or clean or filterThis or clean_data functions as advertised by dated tutorials in one form or the other:

It's mostly the bound parameters which can simplify code. And with excellent tutorials (as seen above) the transition to PDO isn't overly arduous.

More importantly you can pass $_REQUEST[] variables safely behind any query. When submitted <form> fields match the database structure exactly it's even shorter:

Most glaring bug here is the lack of documentation. More significantly the order of filtering was in exactly the wrong order.

Move your variables out of the SQL string.

Often string variables aren't just interpolated into SQL, but concatenated with escaping calls in between.

Once you have converted all mysql_ calls to pdo_query with bound params, remove all redundant pdo_real_escape_string calls.

Or better yet a direct and complete array retrieval:

Remember that pdo_* still allows either or. Just don't escape a variable and bind it in the same query.

Remove the mysql_ function prefix everywhere and replace it with pdo_.

Simply include_once("pdo_mysql.php"); in each invocation script that has to interact with the database.

So much simplicity. But let's get back to some more rewriting advises and technical reasons on why you may want to get rid of mysql_ and escaping.

Thanks. I'm mainly fond of the abstract pictograms visualizing parameterized queries. But I'll probably expand more on a later second conversion from pdo_query() to real PDO:: usage.

The analytic and synthetic reasons were already mentioned. For newcomers there's a more significant incentive to stop using the dated mysql_ functions.

The original implementation in PHP2/FI introduced it explicitly with just "quotes will be automatically escaped making it easier to pass form data directly to msql queries". Notably it was accidentially safe to use with mSQL, as that supported ASCII only. Then PHP3/Zend reintroduced magic_quotes for MySQL and misdocumented it. But originally it was just a convenience feature, not intend for security.

These pdo_* wrapper functions make a coding-friendly stop-gap API. (It's pretty much what MYSQLI could have been if it wasn't for the idiosyncratic function signature shift). They also expose the real PDO at most times. Rewriting doesn't have to stop at using the new pdo_ function names. You could one by one transition each pdo_query() into a plain $pdo->prepare()->execute() call.

Unless you also introduce parameter binding or can utilize something else from the nicer API, it's a pointless switch. I hope it's portrayed simple enough to not further the discouragement to newcomers. (Education usually works better than prohibition.)

Using <pdo_mysql.php> you can switch from the old mysql_ functions with minimal effort. It adds pdo_ function wrappers which replace their mysql_ counterparts.

While it qualifies for the simplest-thing-that-could-possibly-work category, it's also still very experimental code. I just wrote it over the weekend. There's a plethora of alternatives however. Just google for PHP database abstraction and browse a little. There always have been and will be lots of excellent libraries for such tasks.

While this answer stresses that you should care about the readability advantages of dropping mysql_. There's occasionally also a performance advantage (repeated INSERTs with just differing values) due to this visible and technical data/code separation.

With ? placeholders applied you don't have to bother with that:

With bound parameters you separate SQL code and SQL-context values in your PHP code. But it doesn't get shuffled up again behind the scenes (except with PDO::EMULATE_PREPARES). Your database receives the unvaried SQL commands and 1:1 variable values.

You just need a less unwieldy API.

You may have to keep the rest of your sanitize() function for now if your database and application flow expect HTML-context-safe strings. Add a comment that it applies only HTML escaping henceforth.

pdo_query() adds very facile support for bound parameters. Converting old code is straightforward:

Note