How to prevent SQL injection?

A friend suddenly asked me about the vulnerability on their company's website and reported it to wooyun. (Then I learned about the vulnerability with my sister. PS: My sister is a php programmer.) two vulnerabilities were submitted on wooyun, and one was SQL injection, their company...

1. A friend suddenly asked me about the vulnerability on their company's website and reported it to wooyun. (Then I learned about the vulnerability with my sister. PS: My sister is a php programmer)

2. Two vulnerabilities are submitted on wooyun, and one is SQL injection.(After learning, the company uses an old 11-year framework, ormysql_query()These old mysql functions)The other is cookie. the girl writes the user's uid and other sensitive information into the cookie, 2333. Then, the uid of the php processing business logic is also taken from the cookie, 233333 (resulting in the cookie being modified and disguised as any user)

3. I told her about the SQL injection solution (the first type is the temporary solution, which matches SQL statements with regular expressions to filter dangerous characters, keywords, escape characters, and the second solution, abandon the old mysql function and use PDO or mysqli (vulnerability in the cookie location, I suggest that she store uid and other sensitive information in the session, and then encrypt the sessionID and put it in the cookie)

4. The sister finally said, she probably understood it. it is unrealistic to change the mysql driver of the company framework. The company won't let her change it, so she had to use regular expressions to match SQL statements and filter illegal strings!

5. I went to the Internet to find some SQL statement filtering functions that are known to be very useful.
   Answers from Zhihu

I would like to discuss with you the SQL Injection Prevention posture. do you have some useful functions for SQL injection to filter SQL statements? thank you!

Reply content:

1. A friend suddenly asked me about the vulnerability on their company's website and reported it to wooyun. (Then I learned about the vulnerability with my sister. PS: My sister is a php programmer)

2. Two vulnerabilities are submitted on wooyun, and one is SQL injection.(After learning, the company uses an old 11-year framework, ormysql_query()These old mysql functions)The other is cookie. the girl writes the user's uid and other sensitive information into the cookie, 2333. Then, the uid of the php processing business logic is also taken from the cookie, 233333 (resulting in the cookie being modified and disguised as any user)

3. I told her about the SQL injection solution (the first type is the temporary solution, which matches SQL statements with regular expressions to filter dangerous characters, keywords, escape characters, and the second solution, abandon the old mysql function and use PDO or mysqli (vulnerability in the cookie location, I suggest that she store uid and other sensitive information in the session, and then encrypt the sessionID and put it in the cookie)

4. The sister finally said, she probably understood it. it is unrealistic to change the mysql driver of the company framework. The company won't let her change it, so she had to use regular expressions to match SQL statements and filter illegal strings!

5. I went to the Internet to find some SQL statement filtering functions that are known to be very useful.
   Answers from Zhihu

I would like to discuss with you the SQL Injection Prevention posture. do you have some useful functions for SQL injection to filter SQL statements? thank you!

Why not use preprocessing ??????????
Why not use preprocessing ??????????
Why not use preprocessing ??????????
The important thing is said three times!
SQL injection has been a task of the last century!
If you do not want to rewrite the code, you can use a self-encapsulated PHP class. select one of them.
(I found some traces of my project in the code, so I deleted the irrelevant code)
MySQLi version:

  SQL = $ SQL; $ this-> fetch = $ Fetch; $ this-> param = $ Param; // The database information is stored in the configuration file, please modify it to the correct path and value require ($ _ SERVER ['document _ root']. '/configs/config. inc. php '); $ this-> dbhost = & $ DBHost; $ this-> dbuser = & $ DBUser; $ this-> dbpw = & $ DBPassword; $ this-> dbname = & $ DBName;} public function Query () {$ Mysqli = new mysqli ($ this-> dbhost, $ this-> dbuser, $ this-> dbpw, $ this-> dbname); if ($ Mysqli-> connect_errno ){ Echo 'unable to connect to database'; return false;} $ Mysqli-> query ('set NAMES utf8'); $ Mysqli-> begin_transaction (MYSQLI_TRANS_START_READ_ONLY ); $ Stmt = $ Mysqli-> stmt_init (); $ Stmt-> prepare ($ this-> SQL); if (count ($ this-> param)> 0) {$ Type = ''; for ($ I = 0; $ I <count ($ this-> param); $ I ++) {if (is_double ($ this-> param [$ I]) {$ Type. = 'd';} else if (is_int ($ this-> param [$ I]) {$ Type. = 'I';} else if (is_string ($ this -> Param [$ I]) {$ Type. ='s ';} else {$ Type. = 'B' ;}}$ RefArg = array ($ Type); for ($ I = 0; $ I <count ($ this-> param); $ I ++) {$ RefArg [] = & $ this-> param [$ I];} call_user_func_array (array ($ Stmt, 'bind _ param'), $ RefArg);} if (! $ Stmt-> execute () {echo 'an error occurred while reading the database :'. $ Stmt-> error; echo $ this-> SQL; print_r ($ this-> param); $ Mysqli-> rollback (); return false ;} $ Mysqli-> commit (); if (strtolower (substr ($ this-> SQL, 0, 6) = 'select ') {$ this-> res = $ Stmt-> get_result (); $ Stmt-> free_result (); return $ this-> GetRes ();} else {$ Stmt-> free_result (); return true ;}} public function GetRes () {switch (strtolower ($ this-> fetch) {case 'All': $ row = $ this-> res-> fetch_all (); break; case 'array': $ row = $ this-> res-> fetch_array (); break; case 'assoc ': $ row = $ this-> res-> fetch_assoc (); break; case 'field ': $ row = $ this-> res-> fetch_field (); break; case 'Row': $ row = $ this-> res-> fetch_row (); break; default: echo 'Please select a row return mode. '; exit;} return $ row;} public function NumRow () {if (isset ($ this-> res) {return $ this-> Res-> num_rows;} else {return false ;}}}?>

PDO_MYSQL:

  DBHost = & $ DBHost; $ this-> DBUser = & $ DBUser; $ this-> DBPW = & $ DBPassword; $ this-> DBName = & $ DBName; $ this-> SQL = $ SQL; $ this-> Fetch = $ Fetch; $ this-> Param = $ Param;} public function Query () {try {$ Pdo = new PDO ('MySQL: host = '. $ this-> DBHost. '; dbname = '. $ this-> DBName, $ this-> DBUser, $ this-> DBPW); $ Pdo-> query ('set NAMES utf8'); $ Pdo-> beginTransaction (); $ Stmt = $ Pdo-> prepare ($ this-> SQL); if (Count ($ this-> Param)> 0) {for ($ I = 0; $ I <count ($ this-> Param); $ I ++) {$ Stmt-> bindParam ($ I + 1, $ this-> Param [$ I]) ;}} if (! $ Stmt-> execute () {echo 'an error occurred while reading the database :'. $ Stmt-> errorinfo () [2]; $ Pdo-> rollback (); return false;} $ Pdo-> commit (); if (strtolower (substr ($ this-> SQL, 0, 6) = 'select' | strtolower (substr ($ this-> SQL, 0, 4 )) = 'desc') {$ this-> Res = $ Stmt; return $ this-> GetRes ();} else {return true ;}} catch (PDOException $ e) {echo 'unable to connect to database'; $ Pdo-> rollback (); return false ;}} public function GetRes () {switch (Strtolower ($ this-> Fetch) {case 'all': $ Row = $ this-> Res-> fetchAll (PDO: FETCH_ASSOC); break; case 'array': $ Row = $ this-> Res-> fetch (PDO: FETCH_BOTH); break; case 'assoc ': case 'field ': $ Row = $ this-> Res-> fetch (PDO: FETCH_ASSOC); break; case 'Row': $ row = $ this-> Res-> fetch (PDO :: FETCH_NUM); break; default: echo 'Please select a row return mode. '; exit;} return $ Row;} public function NumRow () {I F (isset ($ this-> Res) {return $ this-> Res-> num_rows;} else {return false ;}}?>

Usage:

$ DB = new DB (SQL statement, result set method, array (parameters to be bound); $ DB-> Query ();

Note !!! SQL statement? The parameter to be queried is replaced !!! The SQL injection vulnerability occurs because the SQL statement concatenates variables !!!
PS: If you cannot change the framework, you should wash and sleep. The time will only eliminate regular and outdated technologies.
Do not expect regular expressions to filter SQL statements to completely prevent SQL injection. the reason why PHP7 gave up MySQL extension is that this extension has a security vulnerability!

I have already written a similar summary before, so I will not copy it. common Web security prevention suggestions are available (I will continue to add some omissions later). For details, refer to: PHP security coding.

A simple understanding is that PDO can be used for PDO preprocessing, and addslashes can be used for top-up without PDO.

This addslashes () can be used in PHP system functions.

If you do not need regular expression filtering, consider escaping.

In fact, the essence of defense against SQL injection is to escape or intercept sensitive characters from external variables of GPC. You can pre-process the GPC in the framework entry file or the route parsing class and use it for subsequent services. this is easier to implement and better. As for the cookie, use some encryption methods to test the cookie. the encryption key can be stored in the session.

Well, this should be like this:

1. Add some security services outside, such as jiasule (we use jiasule, the response is usually 100 milliseconds more, occasionally fluctuating, security is just the foundation, some post tasks cannot be intercepted by some xss)

2. Install web application firewall software on the server

3. Evaluate the time, migrate to pdo slowly, and bind parameters to the query (I personally think it is not necessary to change it, but it takes time to arrange this)

Because PHP> = 5.5 has been abolishedmysql_*Therefore, we should take the time to refactor the framework for compatibility considerations.

Www.oneasp.com