Comparing prepare() vs query() with mysqli

Asked

Viewed 1,493 times

7

I went to make an appointment at the bank with query() using mysqli and num_rows to return the number of lines, see the code:

$consulta = $mysqli -> query("SELECT * FROM tabela WHERE Pedido = '$pedido' AND Email = '$email' ");
$linhas = $consulta->num_rows;
echo $linhas;

So I decided to use the Prepared statements to experiment, and the code got bigger, like this:

$consulta = $mysqli -> prepare("SELECT * FROM tabela WHERE Pedido = ? AND Email = ?");
$consulta -> bind_param("ss",$pedido,$email);
$consulta -> execute();
$res = $consulta->get_result(); <----------
$linhas = $res->num_rows;

See the line in the code above, help me understand what makes this function?

Why when I use query, I don’t need to use get_result()?

So Prepared statement is like, preparing the query(prepare()), suggest parameters with bind_params(), executar() and still to get the results using get_result()?

  • 1

    This may be one of the problems caused by sql Injection: http://answall.com/questions/3864/como-prevenir-inje%C3%A7%C3%A3o-de-c%C3%B3digo-sql-no-meu-c%C3%B3digo-php#comment-74490. Another is already more 'aesthetic' in a more complex sql statement its string becomes a sea of quotes, commas and concatenations.

2 answers

10


In general it is safer. Not that we can not give security with the query normal but many do not know how to do it. It’s true that a lot of people who don’t know how to do it don’t care about safety either, they only care if the code appears to be working. Using this form you avoid SQL Injection.

In addition it is possible to have some performance gain because it can be compiled and curled. But depending on the usage pattern will be no different than an unprepared query. In other cases this possible gain will not even be taken advantage of. Remember that the preparation lasts only during the usually short session.

It seems that the data is also trafficked slightly more efficiently but I have no proof of this.

Some people question some of those advantages. Even security could eventually be compromised one day without you having any control over it. Still there is a general recommendation to use prepared consultations.

There are several ways to use the results.

If you think the code has gotten big, create a function that encapsulates the complexity. I notice in PHP programmers the perception that functions should be used. It is rare to see people creating utilitarian functions to simplify code. I see all the time people copying and pasting snippets of code that do the same thing.

It was the choice of the API not to generate the object with results directly, possibly to give more flexibility. In general more flexible code are usually a little larger. We can say that in the case of the query "pure" the get_result() is executed within the query() and it already returns what you want. That’s why I say that you can create a function that deals with all this and turn the 4 lines into just 1.

  • Well, wait a minute. You say: "Remember that the preparation lasts only during the usually short session." Does this mean that the query cache only lasts in the user session? if it drops out, log in again the next day, there will be no cache of the same query, so the server will have to prepare and everything else (use more resources) to store in cache later again and reuse until the session is over?

  • 1

    Cache by definition has undetermined duration. Cache is not something you create to be guaranteed to reuse. Whoever handles the cache (Mysql in this case) takes care of it the way you want and it gives you no guarantees of anything. He does what he thinks is best and usually knows best. If he doesn’t keep something in the cache, it’s because he shouldn’t keep it. Of course this isn’t always perfect but it’s better than most people would do. Don’t worry so much about performance in this case. The difference will be minimal and almost always imperceptible.

  • 1

    @Alexandrec.Caus "For complex queries, this process can consume enough time to make the application noticeably slower if there is a need to repeat the same query often with different parameters". I have seen several times routines increase noticeably the performance by going to use parameters. I don’t know how much Mysql is able to benefit from this - Oracle and SQL Server do a great job (SQL Server even reuses the execution plan between sessions - the query a user executes can get faster if it has already been executed by another user).

  • 1

    @Alexandrec.Caus This answer presents a difference case between using and not using parameters

8

The great advantage of Prepared statements is the following:

  • The query needs to be parsed (Parsed) or prepared only one but can be performed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize your plan for the execution of query. For complex queries, this process can consume time sufficient to make the application noticeably slower if there is a need to repeat the same query many times with different parameters. Using a pre-prepared command (Prepared statement) to application avoids repetition of the cycle of analysis/compilation/optimization. This means that pre-prepared commands use fewer resources and so they run faster.

  • The pre-prepared command parameters (Prepared statements) nay must be between apostrophes (or "single quotation marks" or "quotation marks"), driver will handle this automatically. If an application uses exclusively pre-prepared commands, the developer may have certainty that it will not occur SQL Injection (however, if others parts of the query are being built through non input treated, will still be subject to SQL Injection).

Source: http://php.net/manual/en/pdo.prepared-statements.php

In practice, what changes is exactly what you posted in the examples above. When you run the query with query(), you may be extracting the result using fewer lines of code but you don’t take advantage of a Mysql resource which is exactly what the quote says.

Already using the function prepare(), you just "tie" (bind()) arguments and executes a query that has probably run a million times on your server. Mysql knows the best execution plan for the query and reuses it every time the query is all over again, streamlining your application.

  • So with Prepared statements, can I use less server resources to do a query from the second time? If I have a search on the site, and there I make use of Prepared statements, in this case, it is more remarkable the increase of performance, ie, faster queries for users who search, or the application of Prepared statements is for more specific cases?

  • 3

    This is the main point of the Prepared statements. The use of placeholders (the ?) makes every query equal, changing only the parameters. Take a look at this article: https://vividcortex.com/blog/2014/11/analyzing-prepared-statement-performance-with-vividcortex/

  • Just complementing what I asked you up there.. I meant, a user enters my site, performs a query, and then another user enters.. The server will need to repeat all those processes to execute a query, or it already goes the smaller way, ie.. The server recognizes the Query, or the client when searching for the first time the server will always need to carry out all the query processes, which only from the second query of that Client that the server goes the smaller way..? Sorry if you’re confused..

  • 2

    The first time the query is executed, the server mounts the execution plan and caches it. In subsequent queries, the server recognizes the query, regardless of whether it is the same user or another user. I guess that’s what you were wondering, wasn’t it?

  • Perfect, thanks! I will read more about what you recommended to improve knowledge!

Browser other questions tagged

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