Difference between prepare() and query()?

Question

I was making a select in a table.

I went to see the result, returned false. Then I used the query and it worked.

What’s the difference between the two?

<?php
   $query = DB::getConn()->prepare('select * from tabela where id in ('.$id.')');

   /*o prepare retornou false*/

   $query = DB::getConn()->query('select * from tabela where id in ('.$id.')');
 ?>

Answer 1

prepare() uses Prepared statements a made query it is optimized by the database and can be executed N times what changes are the arguments, its use avoids problem with sql Injection since used correctly.

query() performs a simple query, to each call a query is sent to the bank, when using this method the programmer is responsible for sanitizing the past values.

Both return false, in case the query fails, for more details about the error calls the method errorInfo().

The correct use would be to set an intorrocation for each value and pass an array to execute() for practicality, this can also be done with bindValue() or bindParam() if there is any need.

Obs: with the code of the first two lines, a separate method can be created only to generate the queries.

$ids = array(1,2,5,8);
$binds = implode(",", array_fill(0, count($ids), '?'));

$query = DB::getCon()->prepare('select * from tabela where id in ('. $binds .')' );
$query->execute($ids);

If you want to compare only with id, you can simplify this way.

$query = DB::getCon()->prepare('select * from tabela where id = ?');
$query->execute(array(1));
//a partir do php5.4
//$query->execute([1]);

Recommended reading:

Why parameterized SQL queries (name = ?) prevent SQL Injection?

Using PDO is the safest way to connect to a PHP BD?

How to prevent SQL code injection into my PHP code

How to use PDO bindParam in query IN()?

Dynamic bind

Answer 2

Using the method prepare(), the values for the query are passed through parameters, which are dealt with in part by the query. Queries using this method are preferred when the desired parameters are dynamic.

With the method query() the queries are executed together with the values passed in it, without any internal treatments, and the part dealing with these values so as to make them safe for the query, are left to the programmer’s choice.

Using the Prepared statments with the PDO can use two types of placeholders or parameters:

• The question mark ( ? ).
• The two points followed by the name of the desired parameter ( :name ).

The two types of parameters cannot be used in the same query SQL, one should be chosen, and only use this parameter in the current query, and the values passed should not be executed directly in the query.

Another thing is the fact that the PDO will emulate the Prepared statments for drivers not supported by it natively, and not all drivers support both types.

Using the question mark ( ? ):

$query = DB::getConn()->prepare('select * from tabela where id in (?)');
$query->execute(array(1));

Using the named parameter ( :name ):

$query = DB::getConn()->prepare('select * from tabela where id in (:nome)');
$query->execute(array(':nome'=>1));

Using the question mark for a number of unknown parameters:

$values = array(1,2,3,4,5,6,...n);

foreach ($values as $val)
{
    $params[] = '?';
}

$query = DB::getConn()->prepare('select * from tabela where id in ('.implode(",", $params).')');
$query->execute($values);

Multiple entries for 2 specific SQL table fields:

foreach($values as $id=>$val){
$params[] = '(?, ?)';
$binds['campo1' . $i] = $val; 
$binds['campo2' . $i] = $val;   
$i++;
}

$sql = "INSERT INTO x (campo1, campo2) VALUES ". implode(",", $params);

Or, several named parameters, for 2 specific fields of the SQL table:

foreach($values as $id=>$val){
    $params[] = '(:campo1' . $i . ', :campo2' . $i . ')';
    $binds['campo1' . $i] = $val; 
    $binds['campo2' . $i] = $val;   
    $i++;
}

$sql = "INSERT INTO x (campo1, campo2) VALUES ". implode(",", $params);

Some references:

Writing Mysql script with PHP and PDO

PDO Prepared Statments - PHP.net

PDO Query - PHP.net

Answer 3

Query()

With the function $stmt->query() the queries are executed together with the values passed in it, without any internal treatments, and the part dealing with these values so as to make them safe for the query, are left to the choice of the programmer. If in case you will not pass parameters like GET or POST, etc... this is a good option.

Prepare( )

The function $stmp->prepare(), is called "Prepare Instatement" (Prepared Instructions).

The prepared statement is a resource used to execute the same (or similar) SQL statements repeatedly with high efficiency. Prepared statements work basically like this:

Prepare: A SQL statement template is created and sent to the database. Certain values are not specified, called placeholder parameters (identified as "?" or ":variable").

For example: INSERT INTO TABELA VALUES (?,?,?)

The database analyzes, compiles and executes an optimized query in the SQL statement template and stores the result without running it.

Following are 4 examples of use with prepare statment through the object PDO:

    <?php
    /* Example one: Execute a prepared statement with placeholder */
    $sql = 'INSERT INTO tabela (cod, nome, email) VALUES (:codigo, :nome, :email)';

    $sth = $dbh->prepare($sql);
    $sth->execute(array(':codigo' => (int) 128,
                        ':nome' => 'ndroid',
                        ':email' => '[email protected]'));

  /* Example two: Execute a prepared statement with bind param method */
    $sql = 'INSERT INTO tabela (cod, nome, email) VALUES (:codigo, :nome, :email)';

    $valor1 = '128';
    $valor2 = 'ndroid';
    $valor3 = '[email protected]';

    $sth = $dbh->prepare($sql);
    $sth->bindParam(":codigo", $valor1, PDO::PARAM_INT);
    $sth->bindParam(":nome", $valor2, PDO::PARAM_STR);
    $sth->bindParam(":email", $valor3, PDO::PARAM_STR);
    $sth->execute();

 /* Example three: Execute a prepared statement with bind value method */
    $sql = 'INSERT INTO tabela (cod, nome, email) VALUES (:codigo, :nome, :email)';

    $sth = $dbh->prepare($sql);
    $sth->bindValue(":codigo", (int) 128, PDO::PARAM_INT);
    $sth->bindValue(":nome", 'ndroid', PDO::PARAM_STR);
    $sth->bindValue(":email", '[email protected]', PDO::PARAM_STR);
    $sth->execute();

 /* Example four: Execute a prepared statement with sequence */
    $sql = 'INSERT INTO tabela (cod, nome, email) VALUES (?, ?, ?)';

    $sth = $dbh->prepare($sql);
    $sth->execute(array((int) 128,
                        'ndroid',
                        '[email protected]'));
    ?>

Execute: After receiving the prepared query, the application connects the values with the associated parameters, and the database executes the instruction. The system can execute the statement as many times as it wants with different values. Compared to executing SQL statements directly.

Prepared statements have two main advantages:

1. prepared statements reduce the analysis time as the preparation in which the query is made only once, although the instruction is executed several times, Linked parameters minimize the bandwidth for the server as you need to send only the parameters at a time, not the entire query.
2. prepared statements are very useful against SQL injections, because the values of the parameters, which are transmitted later use different protocol, do not need to be correctly escaped, by methods such as strip_tags(), preg_replace(), etc. If the original declaration template is not derived from external input, SQL injection will not occur.

- To learn more about the Query function
- Learn more about the Prepare function

Answer 4

Prepare just prepares an instruction and returns a Resource called Prepared Statement, i.e., a pre-compiled SQL statement object that waits only to be executed.

Query executes an instruction and returns one Resource which is a pointer to a resultset.

Prepare is useful to avoid problems with arguments (not only SQL Injection) and allows better use for instructions that need to be run over and over again, greatly increasing performance.

In your code

$query = DB::getConn()->prepare('select * from tabela where id in ('.$id.')');

would not be so useful as the argument is already inserted in the query string.

Using Prepared Statement has disadvantages when debugging, as you won’t be able to recover exactly the SQL Statement with the arguments filled in.