Parameterized design using parameterized query sql in pdo

The methods bindParam() and bindValue() are very similar.
The only difference is that the former uses a PHP variable to bind parameters, while the latter uses a value.
So when using bindParam, the second parameter can only use variable names, not variable values, while bindValue can only use specific values.

Copy the code The code is as follows:

$stm = $pdo->prepare("select * from users where user = :user");
$user = "jack";
//Correct
$stm->bindParam(":user",$user);
//Incorrect
//$stm->bindParam(":user","jack");
//Correct
$stm-> bindValue(":user",$user);
//Correct
$stm->bindValue(":user","jack");

In addition, in the stored procedure, bindParam can be bound to input/output Variables, as below:

Copy code The code is as follows:

$stm = $pdo->prepare("call func(:param1)");
$param1 = "abcd";
$stm ->bindParam(":param1",$param1); //Correct
$stm->execute();

The result after the stored procedure is executed can be directly reflected in the variable.
For those large data block parameters in memory, for performance reasons, the former should be used first.
------------------------------------------------- -
http://zh.wikipedia.org/wiki/%E5%8F%83%E6%95%B8%E5%8C%96%E6%9F%A5%E8%A9%A2
Parameterized query
Parameters Parameterized query (Parameterized Query or Parameterized Statement) refers to using parameters (Parameter) to give values ​​where values ​​or data need to be filled in when designing to connect to the database and access data. This method is currently regarded as the most effective and feasible method. A defense method to prevent SQL injection attacks. Some developers may think that using parameterized queries will make the program more difficult to maintain, or it will be very inconvenient to implement some functions [source request]. However, the additional development costs caused by using parameterized queries are usually They are far less than the heavy losses caused by attacks due to the discovery of SQL injection attack vulnerabilities.
In addition to security factors, parameterized queries often have performance advantages compared to SQL statements that concatenate strings. Because parameterized queries allow different data to reach the database through parameters, thereby sharing the same SQL statement. Most databases cache the bytecode generated by interpreting SQL statements to save the overhead of repeated parsing. If you adopt an SQL statement that concatenates strings, the operation data will be part of the SQL statement and not part of the parameters, and unnecessary overhead will be incurred by repeatedly interpreting the SQL statement.
Table of Contents
* 1 Principle
* 2 How to write SQL instructions
o 2.1 Microsoft SQL Server
o 2.2 Microsoft Access
o 2.3 MySQL
o 2.4 PostgreSQL/SQLite
* 3 How to write client programs
o 3.1 ADO.NET
o 3.2 PDO
o 3.3 JDBC
o 3.4 Cold Fusion
[edit] Principle
When using parameterized queries, the database server will not treat the content of the parameters as part of the SQL instruction for processing, but will complete it in the database The SQL command is compiled and then run with parameters, so even if the parameters contain destructive commands, they will not be run by the database.
[edit] SQL command writing method
When writing SQL commands, use parameters to represent the values ​​that need to be filled in, for example:
[edit] Microsoft SQL Server
The parameter format of Microsoft SQL Server is the "@" character plus the parameter Named, SQL Server also supports the anonymous parameter "?".
SELECT * FROM myTable WHERE myID = @myID
INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1, @c2, @c3, @c4)
[edit] Microsoft Access
Microsoft Access does not support named parameters , only supports the anonymous parameter "?".
UPDATE myTable SET c1 = ?, c2 = ?, c3 = ? WHERE c4 = ?
[edit] MySQL
The parameter format of MySQL is composed of the "?" character plus the parameter name.
UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4
[edit] PostgreSQL/SQLite
The parameter format of PostgreSQL and SQLite is formed by ":" plus the parameter name. Of course, Access-like anonymous parameters are also supported.
UPDATE "myTable" SET "c1" = :c1, "c2" = :c2, "c3" = :c3 WHERE "c4" = :c4
[edit] Client program writing method
Write and use in client code Parameter code, for example:
[edit] ADO.NET
ADO.NET is used within ASP.NET.
SqlCommand sqlcmd = new SqlCommand("INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1, @c2, @c3, @c4)", sqlconn);
sqlcmd.Parameters.AddWithValue("@c1" , 1); //Set the value of parameter @c1.
sqlcmd.Parameters.AddWithValue("@c2", 2); // Set the value of parameter @c2.
sqlcmd.Parameters.AddWithValue("@c3", 3); // Set the value of parameter @c3.
sqlcmd.Parameters.AddWithValue("@c4", 4); // Set the value of parameter @c4.
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
[edit] PDO
PDO is used within PHP. When using the PDO driver, the method of using parameter query is generally:

Copy the code The code is as follows:

// Instantiate the data abstraction layer object
$db = new PDO('pgsql:host=127.0.0.1;port=5432;dbname=testdb');
// Execute prepare on the SQL statement and get the PDOStatement object
$ stmt = $db->prepare('SELECT * FROM "myTable" WHERE "id" = :id AND "is_valid" = :is_valid');
// Bind parameters
$stmt->bindValue(':id ', $id);
$stmt->bindValue(':is_valid', true);
// Query
$stmt->execute();
// Get data
foreach($stmt as $row) {
var_dump($row);
}
[code]
For the specific driver of MySQL, it can also be used like this:
$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $mysqli -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt - > execute();
It is worth noting that although the following method can effectively prevent SQL injection (thanks to the escape of the mysql_real_escape_string function), it is not a true parameterized query. Its essence is still a SQL statement that concatenates strings.
[code]
$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'",
mysql_real_escape_string($Username),
mysql_real_escape_string($Password));
mysql_query($ query);

[edit] JDBC
JDBC is used in Java.
java.sql.PreparedStatement prep = connection.prepareStatement(
"SELECT * FROM `users` WHERE USERNAME = ? AND PASSWORD = ?");
prep.setString(1, username);
prep.setString(2, password) ;
prep.executeQuery();
[edit] Cold Fusion

SELECT *
FROM COMMENTS
WHERE COMMENT_ID =

The above introduces the use of parameterized query SQL in parameterized design pdo, including the content of parameterized design. I hope it will be helpful to friends who are interested in PHP tutorials.