How php prevents SQL injection

How php prevents SQL injection

$ Unsafe_variable = $ _ POST ['User _ input']; Mysql_query ("insert into table (column) VALUES ('". $ unsafe_variable ."')"); This is because you can enter a TABLE similar to VALUE "); drop table;-to convert the query: Insert into table (column) VALUES ('value'); drop table table ;' How can this problem be avoided? Use predefine statements and parameterized queries. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for attackers to maliciously inject SQL statements! There are two options: 1. use PDO (PHP Data Objects ): $ Stmt = $ pdo-> prepare ('select * FROM employees WHERE name =: name '); $ Stmt-> execute (array (': name' => $ name )); Foreach ($ stmt as $ row ){ // Do something with $ row } For more information about how to use pdo to prevent SQL injection, see: learn how to use PDO to query Mysql to avoid the risk of SQL injection. 2. use mysqli: $ Stmt = $ dbConnection-> prepare ('select * FROM employees WHERE name =? '); $ Stmt-> bind_param ('s ', $ name ); $ Stmt-> execute (); $ Result = $ stmt-> get_result (); While ($ row = $ result-> fetch_assoc ()){ // Do something with $ row } PDO (PHP Data object) note that when using PDO to access the mysql database, the true pre-defined statements are not used by default! To solve this problem, you must disable the statements prepared by simulation. An example of using PDO to create a connection is as follows: $ DbConnection = new PDO ('MySQL: dbname = dbtest; host = 127.0.0.1; charset = utf8', 'user', 'pass '); $ DbConnection-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false ); $ DbConnection-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); In the preceding example, the error mode ERRMODE is not strictly required, but we recommend that you add it. This method does not stop when an error occurs and a fatal error occurs. And give developers the opportunity to capture any errors (when a PDOException exception is thrown ). The setAttribute () line is mandatory. it tells PDO to disable the simulation of the pre-definition statement and use the real pre-definition statement. This ensures that the statements and values are not parsed by PHP before being sent to the MySQL database server (attackers have no chance to inject malicious SQL statements ). of course, you can set character set parameters in the constructor options. Note that the 'old' PHP version (5.3.6) ignores character set parameters in DSN. What happens when an explain statement is parsed and compiled by the database server on the SQL prepared statement you pass? Use the specified character (in the preceding example, it is like? Or: name) tells the database engine what you want to filter, and then calls execute to execute the prepared statement combined with the parameter value you specified. The most important thing here is that this parameter value is combined with a pre-compiled statement, rather than an SQL string. the operating principle of SQL injection is that SQL scripts created by spoofing methods include malicious strings sent to the database. therefore, by sending actual separate SQL parameters, you will reduce the risk. when using prepared statements, any parameters you send will only be treated as strings (although the database engine may optimize some parameters, of course, it may eventually be numbers ). in the above example, if the variable $ name contains 'Sara'; DELETE * FROM employees, the result will only be a search string "'Sara'; DELETE * FROM employees ", you will not get an empty table. Another benefit is that if the same statement is executed multiple times in the same session, it will be parsed and compiled only once. Example (using PDO ): $ PreparedStatement = $ db-> prepare ('Insert INTO table (column) VALUES (: column )'); $ PreparedStatement-> execute (array (': column' => $ unsafeValue ));