Methods Bindparam () and Bindvalue () are very similar.
The only difference is that the former uses a PHP variable binding parameter, and the latter uses a value.
So using Bindparam is the second argument that can be used only with the variable name, not the variable value, and Bindvalue to the use of a specific value.
Copy the Code code as follows:
$stm = $pdo->prepare ("SELECT * from users where user =: User");
$user = "Jack";
That's right
$stm->bindparam (": User", $user);
Error
$stm->bindparam (": User", "Jack");
That's right
$stm->bindvalue (": User", $user);
That's right
$stm->bindvalue (": User", "Jack");
In addition, in the stored procedure, Bindparam can be bound to a input/output variable, as follows:
Copy the Code code as follows:
$stm = $pdo->prepare ("Call func (:p aram1)");
$param 1 = "ABCD";
$stm->bindparam (":p aram1", $param 1); That's right
$stm->execute ();
After the execution of the stored procedure, the result can be directly reflected on the variable.
For those large data block parameters in memory, in the performance considerations, the former should be used preferentially.
--------------------------------------------------
Http://zh.wikipedia.org/wiki/%E5%8F%83%E6%95%B8%E5%8C%96%E6%9F%A5%E8%A9%A2
parameterized queries
A parameterized query (parameterized query or parameterized Statement) refers to the use of parameters (Parameter) to give values when designing a link to a database and accessing data, where values or data are required, This approach is now considered to be the most effective defense against SQL injection attacks (SQL injection) attack tactics. Some developers may think that using parameterized queries can make the program less maintainable, or it can be very inconvenient to implement partial functionality [source request], however, the additional development costs resulting from using parameterized queries are often significantly lower than the significant damage caused by the discovery of a SQL injection attack vulnerability.
In addition to security factors, parameterized queries tend to have performance advantages over SQL statements that splice strings. Because parameterized queries enable different data to reach the database through parameters, the same SQL statement is common. Most databases cache the overhead of interpreting the bytecode generated by the SQL statement and saving the duplicate parsing. If you take an SQL statement that stitching strings, you will incur unnecessary overhead by repeatedly explaining SQL statements because the manipulation data is part of the SQL statement rather than part of the parameter.
Directory
* 1 principle
* 2 SQL Instruction Writing method
o 2.1 Microsoft SQL Server
O 2.2 Microsoft Access
O 2.3 MySQL
O 2.4 postgresql/sqlite
* 3 Client program Writing method
O 3.1 ado
O 3.2 PDO
O 3.3 JDBC
O 3.4 Cold Fusion
Principles of [edit]
In the case of parameterized queries, the database server does not treat the contents of the parameter as part of the SQL instruction, but instead runs the parameter after the database has completed compiling the SQL instructions, so even if the parameters contain destructive instructions, they will not be run by the database.
Edit How to write SQL instructions
When composing SQL instructions, parameters are used to represent the values that need to be filled in, for example:
Edit Microsoft SQL Server
The parameter format for Microsoft SQL Server is the "@" character plus the parameter name, and 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 the anonymous parameter "?" is supported.
UPDATE myTable SET c1 =?, C2 =?, C3 =? WHERE C4 =?
Edit Mysql
The parameter format for MySQL is the "?" character plus the parameter name.
UPDATE myTable SET c1 =? c1, C2 =? C2, C3 =? c3 WHERE C4 =? c4
Edit Postgresql/sqlite
The parameters of PostgreSQL and SQLite are formatted with ":" plus the parameter name. Of course, anonymous parameters like Access are also supported.
UPDATE "MyTable" SET "C1" =: C1, "C2" =: C2, "C3" =: C3 WHERE "C4" =: C4
[edit] How to write a client program
Write code that uses parameters in the client code, for example:
Edit ado
Ado. NET is used within ASP.
SqlCommand sqlcmd = new SqlCommand ("INSERT into MyTable (C1, C2, C3, C4) VALUES (@c1, @c2, @c3, @c4)", sqlconn);
sqlcmd. Parameters.addwithvalue ("@c1", 1); The value of the set parameters @c1.
sqlcmd. Parameters.addwithvalue ("@c2", 2); The value of the set parameters @c2.
sqlcmd. Parameters.addwithvalue ("@c3", 3); The value of the set parameters @c3.
sqlcmd. Parameters.addwithvalue ("@c4", 4); The value of the set parameters @c4.
Sqlconn. Open ();
sqlcmd. ExecuteNonQuery ();
Sqlconn. Close ();
Edit Pdo
PDO is used within PHP. When using the PDO driver, the use of parameter queries is generally:
Copy the Code code as follows:
Instantiating data Abstraction Layer objects
$db = new PDO (' Pgsql:host=127.0.0.1;port=5432;dbname=testdb ');
Execute prepare on SQL statement, get Pdostatement object
$stmt = $db->prepare (' select * from ' myTable "WHERE" id "=: ID and" is_valid "=: Is_valid ');
Binding parameters
$stmt->bindvalue (': Id ', $id);
$stmt->bindvalue (': Is_valid ', true);
Inquire
$stmt->execute ();
Get Data
foreach ($stmt as $row) {
Var_dump ($row);
}
[Code]
For MySQL-specific drivers, you can also use 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 important to note that although the following methods can effectively prevent SQL injection (thanks to the escape of the mysql_real_escape_string function), it is not a real parameterized query. The essence is still the SQL statement that spliced the 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 within 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 describes the parametric design of the PDO using parameterized query SQL, including parametric design aspects of the content, I hope the PHP tutorial interested in a friend helpful.
