Use parameterized query SQL in pdo

When parametric query is used, the database server does not regard the parameter content as a part of the SQL command for processing. Instead, parameters are applied to run only after the database completes the compilation of the SQL command, therefore, even if the parameters contain destructive commands, they will not be run by the database. The bindParam () method is very similar to bindValue.
The only difference is that the former uses a PHP variable to bind a parameter, while the latter uses a value.
Therefore, bindParam is the second parameter. only the variable name can be used, but not the variable value. bindValue can use the specific value.
Copy codeThe code is as follows:
$ Stm = $ pdo-> prepare ("select * from users where user =: user ");
$ User = "jack ";
// Correct
$ Stm-> bindParam (": user", $ user );
// Error
// $ Stm-> bindParam (": user", "jack ");
// Correct
$ Stm-> bindValue (": user", $ user );
// Correct
$ Stm-> bindValue (": user", "jack ");

In addition, bindParam can be bound to an input/output variable in the stored procedure, as shown below:
Copy codeThe code is as follows:
$ Stm = $ pdo-> prepare ("call func (: param1 )");
$ Param1 = "abcd ";
$ Stm-> bindParam (": param1", $ param1); // correct
$ Stm-> execute ();

After the execution of the stored procedure, the result can be directly reflected to the variable.
For the big data block parameters in the memory, the performance should be considered first.
--------------------------------------------------
Http://zh.wikipedia.org/wiki/%E5%8F%83%E6%95%B8%E5%8C%96%E6%9F%A5%E8%A9%A2
Parameterized query
Parameterized Query (Parameterized Query or Parameterized Statement) refers to a Parameter (Parameter) used to give a value where a value or data needs to be filled in when the design is connected to the database and accessed data, this method is currently regarded as the most effective way to defend against SQL Injection attacks. Some developers may think that using parameterized queries will make the program less difficult to maintain, or [source requests] will be inconvenient to implement some functions. However, the extra development costs caused by using parameterized queries are generally much lower than the major losses caused by the SQL injection vulnerability being discovered.
In addition to security factors, compared to SQL statements that splice strings, parameterized queries often have performance advantages. Parameterized queries allow different data to arrive at the database through parameters, so as to share the same SQL statement. Most databases cache the bytecode generated by interpreting SQL statements, saving the overhead of repeated parsing. If you use an SQL statement that concatenates strings, the operation data is a part of the SQL statement rather than a part of the parameter, and repeated explanations of SQL statements produce unnecessary overhead.
Directory
* 1 principle
* 2 How to write SQL commands
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
When parametric query is used, the database server does not regard the parameter content as a part of the SQL command for processing. Instead, parameters are applied to run only after the database completes the compilation of the SQL command, therefore, even if the parameters contain destructive commands, they will not be run by the database.
[Edit] SQL instruction writing method
When writing SQL commands, parameters are used to represent the values to be filled in, for example:
[Edit] Microsoft SQL Server
The parameter format of Microsoft SQL Server is "@" followed by the parameter name. SQL Server also supports anonymous parameters "? ".
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, but only supports anonymous parameters "? ".
UPDATE myTable SET c1 = ?, C2 = ?, C3 =? WHERE c4 =?
[Edit] MySQL
The MySQL parameter format is "? "Character with 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 ":" With the parameter name. Of course, anonymous parameters similar to Access are also supported.
UPDATE "myTable" SET "c1" =: c1, "c2" =: c2, "c3" =: c3 WHERE "c4" =: c4
[Edit] client program writing method
Write the code that uses parameters in the 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 );//? Yes ?? The value of @ c1.
Sqlcmd. Parameters. AddWithValue ("@ c2", 2 );//? Yes ?? The value of @ c2.
Sqlcmd. Parameters. AddWithValue ("@ c3", 3 );//? Yes ?? @ C3.
Sqlcmd. Parameters. AddWithValue ("@ c4", 4 );//? Yes ?? The value of @ c4.
Sqlconn. Open ();
Sqlcmd. ExecuteNonQuery ();
Sqlconn. Close ();
[Edit] PDO
PDO is used in PHP. When using the PDO driver, the parameter query method is generally:
Copy codeThe 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 to obtain 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 ();
// Obtain data
Foreach ($ stmt as $ row ){
Var_dump ($ row );
}
[Code]
You can also use the specific MySQL driver as follows:
$ 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 (due to the escape of the mysql_real_escape_string function), it is not a real parameterized query. In essence, it is still an 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.exe cuteQuery ();
[Edit] Cold Fusion

SELECT *
FROM COMMENTS
WHERE COMMENT_ID =