I was doing select on a table.
I went to see the result, returned false. Then I used 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.')');
?>
prepare() uses prepared statements a made the query it is optimized by the bank and can be executed N times what changes are the arguments, its use avoids problem with sql injection since it used correctly.
query() performs a simple query, with each call a query is sent to the database, using this method the programmer is responsible for sanitizing the past values.
Both return false, if the query fails, for more details on the error call the errorInfo ()
Using the prepare() method, the values for the query are passed through parameters, which are partially handled by the query. Queries using this method are preferable when the desired parameters are dynamic.
With the query() method, the queries are executed along with the values passed in it, without any internal treatments, and the part of treating these values in order to make them safe for the query, is left to the choice of the programmer.
PDO you can use two types of placeholders or parameters:
You can not use the two types of parameters in the same query SQL , you must choose one, and only use this parameter in the current query, and the values passed must not be executed directly in the query.
Another thing is the fact that PDO will emulate the prepared statments for the 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 an unknown number of 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 fields in the SQL table:
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
Prepare ()With the
$stmt->query()function the queries are executed along with the values passed in it, without any internal treatments, and the part of treating these values in order to make them safe for the query, is 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.
The $stmp->prepare() function is called "Prepare Instatement."
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 : An SQL statement template is created and sent to the database. Certain values are not specified, called placeholder type parameters (identified as "?" Or ": variable").
For example: INSERT INTO TABELA VALUES (?,?,?)
The database parses, compiles, and executes an optimized query in the SQL statement model and stores the result without executing it.
Here are 4 examples of using prepare statment using the 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 binds the values with the associated parameters, and the database executes the statement. The system can run the statement as many times as it wants with different values. Compared to executing SQL statements directly.
Prepared statements have two main advantages:
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
- To learn more about the Prepare function
Prepare only prepares a statement and returns a resource named Prepared Statement , that is, a precompiled SQL statement object that only waits to execute. >
Query executes a statement and returns a resource that is a pointer to a resultset .
Prepare is useful to avoid problems with arguments (not just SQL injection) and allows better use for instructions that need to be executed repeatedly, greatly increasing performance.
In your code
$query = DB::getConn()->prepare('select * from tabela where id in ('.$id.')');
would not be as useful as the argument is already inserted in the query string.
Using the Prepared Statement has disadvantages at the time of debugging, as you will not be able to retrieve exactly the SQL statement with the arguments filled in.