Method Bindparam () and Bindvalue () are very similar.
The only difference is that the former uses a PHP variable to bind the parameter, and the latter uses a value.
So using Bindparam is the second argument can only use variable names, not variable values, and Bindvalue to be able to use specific values.
Copy 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");
Additionally, in stored procedures, Bindparam can be bound to input/output variables, such as the following:
Copy 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 ();
The results of the stored procedure execution can be directly reflected on the variable.
For those large chunks of data in memory, in the performance considerations, priority should be given to using the former.
--------------------------------------------------
Http://zh.wikipedia.org/wiki/%E5%8F%83%E6%95%B8%E5%8C%96%E6%9F%A5%E8%A9%A2
parameterized queries
parameterized queries (parameterized query or parameterized Statement) refer to the use of parameters (Parameter) to value when designing a link to the database and accessing the data, where the values or data need to be filled in. This approach has now been viewed as the most effective defense against SQL injection attacks (SQL injection) attack techniques. Some developers might think that using parameterized queries, can make a program more difficult to maintain, or it will be very inconvenient to implement partial functionality [source request], however, the additional development cost of using parameterized queries is usually much lower than the significant loss caused by the attack of SQL injection attack vulnerabilities.
In addition to security factors, parameterized queries tend to have a performance advantage over the SQL statements that concatenate strings. Because parameterized queries allow different data to reach the database through parameters, the same SQL statement is common. Most databases cache the cost of repeating parsing by caching the bytecode generated by the SQL statement. If you take an SQL statement that is a concatenation string, you will incur unnecessary overhead by repeatedly interpreting the SQL statement because the operation data is part of the SQL statement rather than as 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.net
O 3.2 PDO
O 3.3 JDBC
O 3.4 Cold Fusion
[Edit] Principle
In the case of a parameterized query, the database server does not treat the contents of the parameter as part of the SQL instruction, but instead runs the parameters after the database completes compiling the SQL instructions, so that even if the parameter contains a destructive instruction, it is not run by the database.
Edit How to write SQL instructions
When composing an SQL directive, you use parameters 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 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 anonymous parameter "?".
UPDATE myTable SET c1 =?, C2 =?, C3 =? WHERE C4 =?
Edit Mysql
The parameter format for MySQL is based on the "?" character plus the parameter name.
UPDATE myTable SET c1 =? c1, C2 =? C2, C3 =? c3 WHERE C4 =? c4
Edit Postgresql/sqlite
The PostgreSQL and SQLite parameters are formatted with ":" plus the name of the parameter. Of course, anonymous parameters like Access are also supported.
UPDATE "MyTable" SET "C1" =: C1, "C2" =: C2, "C3" =: C3 where "C4" =: C4
[Edit] Client program writing method
Compose code that uses parameters in client 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); The value to set the parameters @c1.
sqlcmd. Parameters.addwithvalue ("@c2", 2); The value to set the parameters @c2.
sqlcmd. Parameters.addwithvalue ("@c3", 3); The value to set the parameters @c3.
sqlcmd. Parameters.addwithvalue ("@c4", 4); The value to set the parameters @c4.
Sqlconn. Open ();
sqlcmd. ExecuteNonQuery ();
Sqlconn. Close ();
Edit Pdo
PDO is used within PHP. When using PDO drivers, parameter queries are typically used in the following ways:
Copy Code code as follows:
Instantiating a data Abstraction Layer object
$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 worth noting that the following methods, while effectively preventing SQL injection (thanks to the escape of the mysql_real_escape_string function), are not really parameterized queries. Its essence is still a concatenation of the string of SQL statements.
[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
<cfquery name= "Recordset1" datasource= "Cafetownsend" >
SELECT *
From COMMENTS
WHERE comment_id =<cfqueryparam value= "#URL. comment_id#" cfsqltype= "Cf_sql_numeric" >
</cfquery>
