Rectangle 27 9

The best method to prevent SQL injections is to use current technology. The MySQL mysql_ family of functions is deprecated and will be removed from PHP in a future revision.

You should use prepared statements with either MySQLi or PDO instead.

These technologies use prepared statements and parameterized queries. SQL statements are parsed by the database server separately from any parameters. It is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

  • MySQLi: $stmt = $dbConnection->prepare('SELECT * FROM table WHERE name = ?'); $stmt->bind_param('s', $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something with $row }
  • PDO: $stmt = $pdo->prepare('SELECT * FROM table WHERE name = :name'); $stmt->execute(array(':name' => $name)); foreach ($stmt as $row) { // do something with $row }

What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name) you tell the database engine what you want to filter on. Then when you call execute the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not a SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course).

I understand the concept of prepared statements but can you explain why it is invalid to filter the $_REQUEST['id'] as an first layer of protection against unwanted params being passed?

The real question is, "Why bother?" Filtering is how we did this in the past, but times change, and we've moved on.

For a numerical value you are telling me it all has to fall in the line of prepared statements & we should all just trust that modern interface methodsthat have flaws that are not apparent until revealedis better? Do you genuinely suggest pure URL params should just be passed along unchecked? That seems like a problem waiting to happen.

That's great, especially the part that says, "...and is much harder to pull off. AFAIK, you almost never see real 2nd order attacks, as it usually easier to social-engineer your way in." And then the comment, "If ALL your queries are parametrized, you're also protected against 2nd order injection. 1st order injection is forgetting that user data is untrustworthy. 2nd order injection is forgetting that database data is untrustworthy (because it came from the user originally)." That puts a cherry on it, don't you think?

Hey folks, Gordon is absolutely right. If you think escaping data for SQL is simply a security measure, then you are in for quite a surprise. Don't escape data for something until you use it in the context it should be escaped for. Otherwise, you are just making a mess of your data! Also, second order attacks should never be possible if you build your system right and use prepared queries. You may think it isn't a problem... but it is. Even if you don't see it as a security problem, remember that a simple quote mark in a field will screw you up later. Maybe not malicious, but broken at least.

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection
Rectangle 27 9

The best method to prevent SQL injections is to use current technology. The MySQL mysql_ family of functions is deprecated and will be removed from PHP in a future revision.

You should use prepared statements with either MySQLi or PDO instead.

These technologies use prepared statements and parameterized queries. SQL statements are parsed by the database server separately from any parameters. It is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

  • MySQLi: $stmt = $dbConnection->prepare('SELECT * FROM table WHERE name = ?'); $stmt->bind_param('s', $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something with $row }
  • PDO: $stmt = $pdo->prepare('SELECT * FROM table WHERE name = :name'); $stmt->execute(array(':name' => $name)); foreach ($stmt as $row) { // do something with $row }

What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name) you tell the database engine what you want to filter on. Then when you call execute the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not a SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course).

I understand the concept of prepared statements but can you explain why it is invalid to filter the $_REQUEST['id'] as an first layer of protection against unwanted params being passed?

The real question is, "Why bother?" Filtering is how we did this in the past, but times change, and we've moved on.

For a numerical value you are telling me it all has to fall in the line of prepared statements & we should all just trust that modern interface methodsthat have flaws that are not apparent until revealedis better? Do you genuinely suggest pure URL params should just be passed along unchecked? That seems like a problem waiting to happen.

That's great, especially the part that says, "...and is much harder to pull off. AFAIK, you almost never see real 2nd order attacks, as it usually easier to social-engineer your way in." And then the comment, "If ALL your queries are parametrized, you're also protected against 2nd order injection. 1st order injection is forgetting that user data is untrustworthy. 2nd order injection is forgetting that database data is untrustworthy (because it came from the user originally)." That puts a cherry on it, don't you think?

Hey folks, Gordon is absolutely right. If you think escaping data for SQL is simply a security measure, then you are in for quite a surprise. Don't escape data for something until you use it in the context it should be escaped for. Otherwise, you are just making a mess of your data! Also, second order attacks should never be possible if you build your system right and use prepared queries. You may think it isn't a problem... but it is. Even if you don't see it as a security problem, remember that a simple quote mark in a field will screw you up later. Maybe not malicious, but broken at least.

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection
Rectangle 27 20

You are not escaping correctly. mysql_real_escape_string is for escaping SQL string syntax correctly, but you are simply embedding the value as bare value, not as SQL string. You need:

$qry = "SELECT * FROM products WHERE id = '$id'";

Note the quotes around the id in the query.

If the id is numeric though, casting to a number would be more sensible:

$id = (int)$_GET['id'];
mysql_real_escape_string

It's worth adding that mysql_* functions are deprecated and should not be used anymore.

also its very important to note, that the query requires single quotes to work properly. in case you use double quotes within the query string to sorround the value will render {mysql_real_escape_string()} useless!

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection
Rectangle 27 20

You are not escaping correctly. mysql_real_escape_string is for escaping SQL string syntax correctly, but you are simply embedding the value as bare value, not as SQL string. You need:

$qry = "SELECT * FROM products WHERE id = '$id'";

Note the quotes around the id in the query.

If the id is numeric though, casting to a number would be more sensible:

$id = (int)$_GET['id'];
mysql_real_escape_string

It's worth adding that mysql_* functions are deprecated and should not be used anymore.

also its very important to note, that the query requires single quotes to work properly. in case you use double quotes within the query string to sorround the value will render {mysql_real_escape_string()} useless!

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection
Rectangle 27 2

And there are plenty of answers on this question already

The question is valid. This thread on the topic seems to have a more reasonable perspective on this issue.

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection
Rectangle 27 2

And there are plenty of answers on this question already

The question is valid. This thread on the topic seems to have a more reasonable perspective on this issue.

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection
Rectangle 27 0

Persistent connections can leave 'garbage' behind if the previous user of the connection aborted abnormally. Consider the case where you're doing a transaction with a series of individual queries. Half-way through, let's assume the remote user killed the connection, which causes the script to abort. Because the mysql connection is persistent, it will not be closed when the script exits, and leaves the transaction open.

MySQL has no way of knowing it should do a roll-back, so it sits there with the transaction in play, waiting for another query to come in.

When this connection gets re-used by another script, that script also has no way of knowing that a transaction is in play, and will simply start issuing its own queries.

This can quite easily cause deadlocks, because of all these 'stale' transactions being left partially copmleted. If your scripts change any server-side settings for some reason, those settings will also be persisted on a per-connection basis, etc...

In other words, unless you're EXTREMELY careful with your code, persistent connections will cause a long series of strange behaviors that are very difficult to diagnose, because each connection will be in a different state for every script that uses them.

Slow PHP scripts cause MySQL server to sleep (using pconnect()) - Stac...

php mysql
Rectangle 27 0

The problem is that the persistent connections you're opening with mysql_pconnect are timing out on the MySQL side before they're timing out on the PHP side, causing PHP to try to use zombie connections. You need to make sure that the timeout is longer on the MySQL side than it is on the PHP side to prevent this from happening.

This page also claims you can use mysql_ping() to work around this issue:

You can also just switch to mysql_connect if the performance is fine.

Slow PHP scripts cause MySQL server to sleep (using pconnect()) - Stac...

php mysql
Rectangle 27 0

The persistent connection is held open while doing nothing. If it is set with a short timeout, it will go into sleep state and need to be woken again to be used.

It's very rare to actually require persistent connections, and the time saved by not reopening a connection to the MySQL server in your script seems to be wasted on the sleep/wake process. I would advise you switch to plain old mysql_connect(). If necessary, you can close your database connection and reestablish it later in the script.

If your web server and database server are on the same machine there is virtually no savings with a persistent connection. And according to comments in PHP docs (I know they're not the best resource) Apache misbehaves with mysql_pconnect()

  • Apache does not work well with persistent connections. When it receives a request from a new client, instead of using one of the available children which already has a persistent connection open, it tends to spawn a new child, which must then open a new database connection. This causes excess processes which are just sleeping, wasting resources, and causing errors when you reach your maximum connections, plus it defeats any benefit of persistent connections. (See comments below on 03-Feb-2004, and the footnote at http://devzone.zend.com/node/view/id/686#fn1)

Slow PHP scripts cause MySQL server to sleep (using pconnect()) - Stac...

php mysql
Rectangle 27 0

you should convert your queries into "prepared statements" using PDO or mysqli.

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection
Rectangle 27 0

you should convert your queries into "prepared statements" using PDO or mysqli.

php - How to prevent mysql sleep injections? - Stack Overflow

php mysql sql-injection