MySQL prepared statement vs normal query. Gains & Losses

Question

I'm in the middle of updating/reworking some database code and I was wondering, what I should really expect from using prepared statements.

Take this example code:

$values = '';
for ($i = 0; $i < $count; $i++) {
    $name = mysql_real_escape_string ($list[$i][1]);
    $voc = mysql_real_escape_string ($list[$i][3]);
    $lev = $list[$it][2];
    $lev = is_numeric ($lev)? $lev : 0;

    $values .= ($values == '')? "('$name', '$voc', $lev)" : ", ('$name', '$voc', $lev)";
}
if ($values != '') {
    $core->query ("INSERT INTO onlineCList (name, voc, lev) VALUES $values;");
}

Now, apart from the obvious gain in readability (, sanity) and the fact that max_packet_size stops being an issue, am I supposed to expect any changes in performance when I recode this to use prepared statements? I'm connecting remotely to the MySQL server, and I worry that sending multiple small packets would be significantly slower then sending one big packet. If this is the case, can MySQLi/mysqlnd cache these packets?

Another example:

$names = '';
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
    $name = mysql_real_escape_string($row['name']);

    $names .= ($names == '') ? "'$name'" : ", '$name'";
}
if ($names != '') {
    $core->query ("UPDATE onlineActivity SET online = NULL WHERE name IN ($names) AND online = 1;");
}

As above, should I expect the unexpected, after recoding this to use prepared statements? Does it make any difference for the MySQL server, if it has to run one query with a big IN clause, or multiple prepared queries with equality checks (.. WHERE name = $name AND ..)?

Assume that everything is properly indexed.

Possible duplicate of [Should I use prepared statements for MySQL in PHP PERFORMANCE-WISE?](http://stackoverflow.com/questions/2214408/should-i-use-prepared-statements-for-mysql-in-php-performance-wise) — e4c5, Apr 24 '16 at 02:14

score 13 · Accepted Answer · answered Jun 10 '11 at 03:53

13

Normally, if you just use a prepared statement in place of a plain query, it's marginally slower since the query is prepared and executed in two steps instead of one. Prepared statements become faster only when you're preparing the statement and then executing it multiple times.

However, in this case you're using mysql_real_escape_string, which does a roundtrip to the database. Even worse, you're doing it inside a loop, so, executing it multiple times per query. So, in this case replacing all of those roundtrips with a single prepared statement is a win-win-win.

Regarding your last question, there's no reason you can't use the same query with a prepared statement as you would through the normal query parser (i.e. no reason to execute one version with an IN and the other with a bunch of ORs). The prepared statement can have IN (?, ?, ?), and then you just bind that number of parameters.

My advice would be to always use prepared statements. In cases where they add a marginal performance overhead, they're still worth it for the security (no SQL injection) and readability benefits. For sure, anytime you find yourself resorting to mysql_real_escape_string, you should use a prepared statement instead. (For simple one-off queries where there's no need to escape variable inputs, they aren't strictly necessary.)

answered Jun 10 '11 at 03:53

joelhardi

11,039
3
32
38

2

Thanks, I didn't know that `mysql_real_escape_string` actually communicates with the server when called; I just assumed that the character encoding is stored when you open a connection, and `mysql_real_escape_string` works locally, using that stored information. – OpiF Jun 10 '11 at 04:02
4

Yeah, it should probably be more prominent in the PHP docs that it does that. The bytestream is sent to MySQL which escapes using its charset settings and returns the escaped string ... if you turn on your MySQL server's query log you'll see all of these "queries." There's the old [mysql_escape_string](http://php.net/manual/en/function.mysql-escape-string.php) which works locally, but it doesn't respect the charset and is deprecated. If you think about it, it makes sense, only MySQL can escape string data using its exact charset implementation. An obvious case for prepared statements instead. – joelhardi Jun 10 '11 at 04:30
1

@joelhardi Your comments about `mysql_real_escape_string` are really interesting. Does this apply to the mysqli counterpart too? Are there any benchmarks that show what the overhead is? I've always thought using prepared statements when you _only_ plan to execute a query once doesn't make sense so would be interested to know how many real_escape_strings you'd need to run vs a prepared statement executed once to make the latter more performant. I'm guessing it's micro optimisation but would be interested all the same. – texelate Nov 23 '16 at 07:48
mysql_real_escape_string doesn't send anything to the server. It escapes the passed string on client side considering the actual character set. – Georg Richter Nov 27 '20 at 23:24
@GeorgRichter I have no idea what evidence you have for that statement, it absolutely does. Turn on your MySQL query log, you'll the queries on your database server. [This behavior is documented](https://www.php.net/manual/en/function.mysql-real-escape-string.php), and you'll see that `mysql_real_escape_string` doesn't even function without a database connection (instead it returns `false`). As a side note, it's 2020! No one should be using PHP 5.x, or the original PHP MySQL extension, or `mysql-real-escape-string` any more. – joelhardi Nov 29 '20 at 01:02
@joelhardi: Since I wrote that stuff (including ext/mysqli). mysql_real_escape_stirng needs a connection handle to determine the character set used by the connection, and escapes the passed string consideing the character set of the connection (e.g. gbr character 0xbf27 which contains a backslash 0x27 will not be escaped by an additional backslash). This happens entirely on client side in all drivers (mysqlnd, libmysql and libmariadb). And there is definetly not a server log entry for mysql_real_escape_string, since it's not supported by protocol. – Georg Richter Nov 30 '20 at 06:35

score -1 · Answer 2 · edited May 23 '17 at 12:07

-1

Prepared statements are more secure.
Prepared statements have better performance.
Prepared statements are more convenient to write.

Will you read all these!!

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

try this too

Prepared Statement vs. Stored Procedure

edited May 23 '17 at 12:07

Community

1
1

answered Jun 10 '11 at 03:20

zod

12,092
24
70
106

11

2. Depends on the context. 3. That's your opinion. – texelate Nov 23 '16 at 07:45
2

While prepared statement are great (especially if you have security issues like user inputs), texelate is fully right, and also forget to mention that they are **less** convinient to read/debug. Personally, I rarely manipulate user input, and try to avoid preparing queries as much as I can, usually because it wouldn't bring me more than hassle when needing to fix a failing request *without having its actual SQL contents*. They remain great, but there is *also* reasons not to use them :) – Balmipour Jul 04 '17 at 16:44

MySQL prepared statement vs normal query. Gains & Losses

2 Answers2

Linked