Rectangle 27 636

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

So, let's start off by showing the attack...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Selecting a Character Set

$pdo->query('SET NAMES gbk');

For this attack to work, we need the encoding that the server's expecting on the connection both to encode ' as in ASCII i.e. 0x27 and to have some character whose final byte is an ASCII \ i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbk and sjis. We'll select gbk here.

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string '. Note that in latin1 and gbk, 0x27 on its own is a literal ' character.

We have chosen this payload because, if we called addslashes() on it, we'd insert an ASCII \ i.e. 0x5c, before the ' character. So we'd wind up with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

The C API call to mysql_real_escape_string() differs from addslashes() in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

Therefore the call to mysql_real_escape_string() inserts the backslash, and we have a free hanging ' character in our "escaped" content! In fact, if we were to look at $var in the gbk character set, we'd see:

Which is exactly what the attack requires.

This part is just a formality, but here's the rendered query:

SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that insteadbut it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$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("\xbf\x27 OR 1=1 /*"));
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

I wasn't aware of emulated prepared statements. Great work +1

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

I was scrolling down and wondering about who will appear under that great post, and as you guess.. :)

NO_BACKSLASH_ESCAPES

@slevin the "OR 1=1" is a placeholder for whatever you want. Yes, it's searching for a value in name, but imagine the "OR 1=1" part was "UNION SELECT * FROM users". You now control the query, and as such can abuse it...

php - Are PDO prepared statements sufficient to prevent SQL injection?...

php security pdo sql-injection
Rectangle 27 629

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

So, let's start off by showing the attack...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Selecting a Character Set

$pdo->query('SET NAMES gbk');

For this attack to work, we need the encoding that the server's expecting on the connection both to encode ' as in ASCII i.e. 0x27 and to have some character whose final byte is an ASCII \ i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbk and sjis. We'll select gbk here.

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string '. Note that in latin1 and gbk, 0x27 on its own is a literal ' character.

We have chosen this payload because, if we called addslashes() on it, we'd insert an ASCII \ i.e. 0x5c, before the ' character. So we'd wind up with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

The C API call to mysql_real_escape_string() differs from addslashes() in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

Therefore the call to mysql_real_escape_string() inserts the backslash, and we have a free hanging ' character in our "escaped" content! In fact, if we were to look at $var in the gbk character set, we'd see:

Which is exactly what the attack requires.

This part is just a formality, but here's the rendered query:

SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that insteadbut it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$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("\xbf\x27 OR 1=1 /*"));
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

NO_BACKSLASH_ESCAPES

php - Are PDO prepared statements sufficient to prevent SQL injection?...

php security pdo sql-injection
Rectangle 27 622

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

So, let's start off by showing the attack...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Selecting a Character Set

$pdo->query('SET NAMES gbk');

For this attack to work, we need the encoding that the server's expecting on the connection both to encode ' as in ASCII i.e. 0x27 and to have some character whose final byte is an ASCII \ i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbk and sjis. We'll select gbk here.

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string '. Note that in latin1 and gbk, 0x27 on its own is a literal ' character.

We have chosen this payload because, if we called addslashes() on it, we'd insert an ASCII \ i.e. 0x5c, before the ' character. So we'd wind up with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

The C API call to mysql_real_escape_string() differs from addslashes() in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

Therefore the call to mysql_real_escape_string() inserts the backslash, and we have a free hanging ' character in our "escaped" content! In fact, if we were to look at $var in the gbk character set, we'd see:

Which is exactly what the attack requires.

This part is just a formality, but here's the rendered query:

SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that insteadbut it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$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("\xbf\x27 OR 1=1 /*"));
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

NO_BACKSLASH_ESCAPES

php - Are PDO prepared statements sufficient to prevent SQL injection?...

php security pdo sql-injection
Rectangle 27 620

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

So, let's start off by showing the attack...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Selecting a Character Set

$pdo->query('SET NAMES gbk');

For this attack to work, we need the encoding that the server's expecting on the connection both to encode ' as in ASCII i.e. 0x27 and to have some character whose final byte is an ASCII \ i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbk and sjis. We'll select gbk here.

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string '. Note that in latin1 and gbk, 0x27 on its own is a literal ' character.

We have chosen this payload because, if we called addslashes() on it, we'd insert an ASCII \ i.e. 0x5c, before the ' character. So we'd wind up with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

The C API call to mysql_real_escape_string() differs from addslashes() in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

Therefore the call to mysql_real_escape_string() inserts the backslash, and we have a free hanging ' character in our "escaped" content! In fact, if we were to look at $var in the gbk character set, we'd see:

Which is exactly what the attack requires.

This part is just a formality, but here's the rendered query:

SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that insteadbut it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$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("\xbf\x27 OR 1=1 /*"));
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

NO_BACKSLASH_ESCAPES

php - Are PDO prepared statements sufficient to prevent SQL injection?...

php security pdo sql-injection
Rectangle 27 617

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

So, let's start off by showing the attack...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Selecting a Character Set

$pdo->query('SET NAMES gbk');

For this attack to work, we need the encoding that the server's expecting on the connection both to encode ' as in ASCII i.e. 0x27 and to have some character whose final byte is an ASCII \ i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbk and sjis. We'll select gbk here.

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string '. Note that in latin1 and gbk, 0x27 on its own is a literal ' character.

We have chosen this payload because, if we called addslashes() on it, we'd insert an ASCII \ i.e. 0x5c, before the ' character. So we'd wind up with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

The C API call to mysql_real_escape_string() differs from addslashes() in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

Therefore the call to mysql_real_escape_string() inserts the backslash, and we have a free hanging ' character in our "escaped" content! In fact, if we were to look at $var in the gbk character set, we'd see:

Which is exactly what the attack requires.

This part is just a formality, but here's the rendered query:

SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that insteadbut it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$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("\xbf\x27 OR 1=1 /*"));
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

I wasn't aware of emulated prepared statements. Great work +1

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

I was scrolling down and wondering about who will appear under that great post, and as you guess.. :)

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

NO_BACKSLASH_ESCAPES

php - Are PDO prepared statements sufficient to prevent SQL injection?...

php security pdo sql-injection
Rectangle 27 617

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

So, let's start off by showing the attack...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Selecting a Character Set

$pdo->query('SET NAMES gbk');

For this attack to work, we need the encoding that the server's expecting on the connection both to encode ' as in ASCII i.e. 0x27 and to have some character whose final byte is an ASCII \ i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbk and sjis. We'll select gbk here.

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string '. Note that in latin1 and gbk, 0x27 on its own is a literal ' character.

We have chosen this payload because, if we called addslashes() on it, we'd insert an ASCII \ i.e. 0x5c, before the ' character. So we'd wind up with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

The C API call to mysql_real_escape_string() differs from addslashes() in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

Therefore the call to mysql_real_escape_string() inserts the backslash, and we have a free hanging ' character in our "escaped" content! In fact, if we were to look at $var in the gbk character set, we'd see:

Which is exactly what the attack requires.

This part is just a formality, but here's the rendered query:

SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that insteadbut it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$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("\xbf\x27 OR 1=1 /*"));
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

NO_BACKSLASH_ESCAPES

php - Are PDO prepared statements sufficient to prevent SQL injection?...

php security pdo sql-injection
Rectangle 27 246

The same drawbacks exist using PDO as with any other PHP database interface that does persistent connections: if your script terminates unexpectedly in the middle of database operations, the next request that gets the left over connection will pick up where the dead script left off. The connection is held open at the process manager level (Apache for mod_php, the current FastCGI process if you're using FastCGI, etc), not at the PHP level, and PHP doesn't tell the parent process to let the connection die when the script terminates abnormally.

If the dead script locked tables, those tables will remain locked until the connection dies or the next script that gets the connection unlocks the tables itself.

If the dead script was in the middle of a transaction, the next script that gets that connection also gets the transaction state. It's very possible (depending on your application design) that the next script might not actually ever try to commit the existing transaction, or will commit when it should not have, or roll back when it should not have.

This is only the tip of the iceberg. It can all be mitigated to an extent by always trying to clean up after a dirty connection on every single script request, but that can be a pain depending on the database. Unless you have identified creating database connections as the one thing that is a bottleneck in your script (this means you've done code profiling using xdebug and/or xhprof), you should not consider persistent connections as a solution to anything.

Further, most modern databases (including PostgreSQL) have their own preferred ways of performing connection pooling that don't have the immediate drawbacks that plain vanilla PHP-based persistent connections do.

To clarify a point, we use persistent connections at my workplace, but not by choice. We were encountering weird connection behavior, where the initial connection from our app server to our database server was taking exactly three seconds, when it should have taken a fraction of a fraction of a second. We think it's a kernel bug. We gave up trying to troubleshoot it because it happened randomly and could not be reproduced on demand, and our outsourced IT didn't have the concrete ability to track it down.

Regardless, when the folks in the warehouse are processing a few hundred incoming parts, and each part is taking three and a half seconds instead of a half second, we had to take action before they kidnapped us all and made us help them. So, we flipped a few bits on in our home-grown ERP/CRM/CMS monstrosity and experienced all of the horrors of persistent connections first-hand. It took us weeks to track down all the subtle little problems and bizarre behavior that happened seemingly at random. It turned out that those once-a-week fatal errors that our users diligently squeezed out of our app were leaving locked tables, abandoned transactions and other unfortunate wonky states.

This sob-story has a point: It broke things that we never expected to break, all in the name of performance. The tradeoff wasn't worth it, and we're eagerly awaiting the day we can switch back to normal connections without a riot from our users.

SELECT orders.* FROM orders LEFT JOIN items USING(item_id)

I know a big website that has been using persistent connections for nearly a decade now. The trick is using a layer above the DB extension, and having it remember the things that need to be cleaned up by using register_shutdown_function(). If the process dies, the connection dies too. If it doesn't, the connection is reset to its clean state (e.g., open transactions are rolled back). If this fails, the connection is closed and a new one will be opened by the next request to the same process. There is no need to demonize persistent connections.

We had a 5 second delay on connect, which we managed to isolate as a DNS + IPv6 problem. The server was looking for an v6 address, failing, and then using the IPv4 address.

php - What are the disadvantages of using persistent connection in PDO...

php pdo
Rectangle 27 9

Persistent connections are a good idea only when it takes a (relatively) long time to connect to your database. Nowadays that's almost never the case. The biggest drawback to persistent connections is that it limits the number of users you can have browsing your site: if MySQL is configured to only allow 10 concurrent connections at once then when an 11th person tries to browse your site it won't work for them.

PDO does not manage the persistence. The MySQL driver does. It reuses connections when a) they are available and the host/user/password/database match. If any change then it will not reuse a connection. The best case net effect is that these connections you have will be started and stopped so often because you have different users on the site and making them persistent doesn't do any good.

The key thing to understand about persistent connections is that you should NOT use them in most web applications. They sound enticing but they are dangerous and pretty much useless.

I'm sure there are other threads on this but a persistent connection is dangerous because it persists between requests. If, for example, you lock a table during a request and then fail to unlock then that table is going to stay locked indefinitely. Persistent connections are also pretty much useless for 99% of your apps because you have no way of knowing if the same connection will be used between different requests. Each web thread will have it's own set of persistent connections and you have no way of controlling which thread will handle which requests.

The procedural mysql library of PHP, has a feature whereby subsequent calls to mysql_connect will return the same link, rather than open a different connection (As one might expect). This has nothing to do with persistent connections and is specific to the mysql library. PDO does not exhibit such behaviour

In General you could use this as a rough "ruleset"::

  • There are only few applications/users accessing the database, i.e. you will not result in 200 open (but probably idle) connections, because there are 200 different users shared on the same host.

The database is running on another server that you are accessing over the network

An (one) application accesses the database very often

Using persistent connections is considerable faster, especially if you are accessing the database over a network. It doesn't make so much difference if the database is running on the same machine, but it is still a little bit faster. However - as the name says - the connection is persistent, i.e. it stays open, even if it is not used.

The problem with that is, that in "default configuration", MySQL only allows 1000 parallel "open channels". After that, new connections are refused (You can tweak this setting). So if you have - say - 20 Webservers with each 100 Clients on them, and every one of them has just one page access per hour, simple math will show you that you'll need 2000 parallel connections to the database. That won't work.

@AdvaitAmin added a resource link for that..

"YES, use persistent connections, if: [...] There are only few applications/users accessing the database" contradicts with "Only use it for applications with lots of requests.". The latter is however correct. Situation: thousands of requests per second will result in hundreds of active database connections. When a system scales linearly, it will also linearly scale the amount of connections to the database. So more requests (more users) will result in more connections. So you need limited(!) yet many active connections when you have lots of requests(users)

php - What are the disadvantages of using persistent connection in PDO...

php pdo
Rectangle 27 1088

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 1077

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 1077

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 1073

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 1068

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 1052

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 1052

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 1050

A simple and short PDO tutorial

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

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).

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.

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

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

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

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).

mysql_*
  • Removed as of PHP 7
  • Doesn't support prepared statements or parameterized queries
  • Doesn't support all of the functionality in MySQL 5.1

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.

MySQLi
PDO
E_DEPRECATED
error_reporting
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED

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

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

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

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

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

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

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.

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.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

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

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

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.

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.

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

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

Below is an example of how you can do it:

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

$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);

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

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.

  • 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::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.
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

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

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());
}

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:

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.
}

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.

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:

So what you are doing in mysql_* is:

<?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'];
}

Now in PDO, you can do this like:

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.

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

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.

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.

$stmt->fetch(PDO::FETCH_ASSOC)

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

  • 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
  • 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

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

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

What we are doing in mysql_* function is:

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

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

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

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

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.

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.

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

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):

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

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

  • 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.

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.

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:

$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

<?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);

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:

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);

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

<?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();

and

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 ?.

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.

SELECT
$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);
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
DELETE
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
UPDATE
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

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:

$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 /*"));

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

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.

@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! :]

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.

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

php mysql database
Rectangle 27 38

A common complain about persistent connections is that their state is not reset before reuse. For example, open and unfinished transactions are not automatically rolled back. But also, authorization changes which happened in the time between putting the connection into the pool and reusing it are not reflected. This may be seen as an unwanted side-effect. On the contrary, the name persistent may be understood as a promise that the state is persisted.

The mysqli extension supports both interpretations of a persistent connection: state persisted, and state reset before reuse. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli_change_user() to reset the state. The persistent connection appears to the user as if it was just opened. No artifacts from previous usages are visible.

The mysqli_change_user() function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECT being set.

It is left to the user to choose between safe behavior and best performance. Both are valid optimization goals. For ease of use, the safe behavior has been made the default at the expense of maximum performance.

+1, if not for the fact that we've cleaned up the mess in other ways, I'd love to see if manually calling change_user would have fixed our bizarre unknown-state issues.

What is the equivalent for PDO Postgres persistent connections? I have similar issues like @Charles had, where after a while users would be getting error like fetch sql - server closed the connection unexpectedly This probably means the server terminated abnormally When running simple SELECT query (not even transactions).

@Carmageddon, that's more suited to a new question, but the tl;dr is that Postgres doesn't do pconnect and you should be using one of the external connection pools instead.

@Charles, what do you mean by that? is using the PDO's persistent connection not equivalent to using "external connection pools"? or what did you mean?

@Carmageddon, what I mean is that the Postgres community settled on connection pooling as a better solution than pconnect. Check out pgbouncer or pgpool-II. I'm not sure that PDO does Postgres pconnect anyway, but I may be totally off my rocker.

php - What are the disadvantages of using persistent connection in PDO...

php pdo
Rectangle 27 1

This is not efficient. Each time you execute a query you make a new Connection object, which in turn creates a new PDO connection. Connecting to the database will give you some overhead. You actually don't need to connect every time. You can just connect once and use that connection for subsequent queries.

So with a small change you can make your database class create the connection on first use:

class Database {

    /**
     * Reference to the connection
     */
    private static $connection = null;

    /**
     * Run a raw query on the database.
     *
     * @param string $query
     * @return array
     */
    public static function query($query)
    {
        // Get the connection
        $conn = self::getConnection();

        // Return the query
        return $conn->query($query);
    }

    public static function getConnection()
    {
      // Create the connection if needed.
      if (self::$connection === null)
      {
        self::$connection = new Connection;
      }
      // Return new or existing instance.
      return self::$connection;
    }

    ...

No it doesn't. It's still static. I think that is not the best solution per se, but that's another discussion altogether, so I didn't want to go there now. :)

Great work GolezTrol! I threw some echo statements in there to try and get a sense of what was going on and it was using the old connection (or existing connection). Thanks again for your help.. coffee is on me.

GolezTrol, I'd be interested in knowing why you don't like about how I've implemented the database statically. To me it just is a lot easier when I write the code, but I understand there may be some repercussions. Can you let me know what those are?

php - How do I make my PDO connection more efficient? - Stack Overflow

php mysql pdo
Rectangle 27 23

PDO query fails but I can't see any errors. How to get an error messag...

To be able to see database errors, one have to set PDO errmode to exceptions. Exceptions are better than regular errors in many ways: they always contains a stack trace, they can be caught using try..catch or handled using dedicated error handler. And even unhandled, they act as regular PHP errors providing all the important information, following site-wide error reporting settings.

Note that setting this mode as a connection option will let PDO throw exceptions on connection errors too, which is very important. So, here is an example for creating a PDO connection right way:

$dsn = "mysql:host=$host;dbname=$db;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // other options 
);
$pdo = new PDO($dsn, $user, $pass, $opt);

Connecting this way, you will be always notified of all database errors, occurred during query execution. Note that you have to be able to see PHP errors in general. On a live site you have to peek into error logs, so, settings have to be

while on a local development server it's ok to make errors on screen:

error_reporting(E_ALL);
ini_set('display_errors',1);

and of course you should never ever use error suppression operator (@) in front of your PDO statements.

Also, due to many bad examples telling you to wrap every PDO statement into try..catch block, I have to make a distinct note:

  • A custom exception handler could be added later, but not required. Especially for new users, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure.
  • Use try..catch only if you are going to handle the error itself - say, to rollback a transaction.

php - Reference — frequently asked questions about PDO - Stack Overflo...

php pdo
Rectangle 27 11

On my tests I had a connection time of over a second to my localhost, thus assuming I should use a persistent connection. Further tests showed it was a problem with 'localhost':

  • localhost: connectDB: 1.0214691162109 (over one second: do not use localhost!)

Interestingly: The following code is just as fast as using 127.0.0.1:

$host = gethostbyname('localhost');
// echo "<p>$host</p>";
$db = new PDO("mysql:host=$host;dbname=" . DATABASE . ';charset=utf8', $username, $password,
    array(PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

Seems like PDO has difficulty on translating domainnames! Thank you, I was wondering why each connection was taking damn long on my quad core machine!

@Gunnar Bernstein +1 nice find. "localhost" certainly does take longer and this has improved my web app's speed somewhat (it makes lots of connections).

localhost uses socket connection, socket connection is famous to being bad on big amount of connections

@mente Any reference, resource that can prove that fact? I would tend to think that UDS are prefered over TCP. Thanks.

php - What are the disadvantages of using persistent connection in PDO...

php pdo