PHP MySQLI Prevent SQL Injection

 

This question already has an answer here:

  • How can I prevent SQL injection in PHP? 28 answers

    • Any query can be injected whether it's read or write, persistent or transient. Injections can be performed by ending one query and running a separate one (possible with mysqli ), which renders the intended query irrelevant.

    Any input to a query from an external source whether it is from users or even internal should be considered an argument to the query, and a parameter in the context of the query. Any parameter in a query needs to be parameterized. This leads to a properly parameterized query that you can create a prepared statement from and execute with arguments. For example: 

    SELECT col1 FROM t1 WHERE col2 = ?

    ? is a placeholder for a parameter. Using mysqli , you can create a prepared statement using prepare , bind a variable (argument) to a parameter using bind_param , and run the query with execute . You don't have to sanitize the argument at all (in fact it's detrimental to do so). mysqli does that for you. The full process would be: 

    $stmt = mysqli->prepare("SELECT col1 FROM t1 WHERE col2 = ?");
$stmt->bind_param("s", $col2_arg);
$stmt->execute();

    There is also an important distinction between parameterized query and prepared statement. This statement, while prepared, is not parameterized and is thus vulnerable to injection: 

    $stmt = mysqli->prepare("INSERT INTO t1 VALUES ($_POST[user_input])");

    To summarize:

  • All Queries should be properly parameterized (unless they have no parameters)
  • All arguments to a query should be treated as hostile as possible no matter their source

    • It will be closed in seconds, but just to make things straight

    I understand how to use mysqli_real_escape_string

    I am afraid you are not.

    if I have to use that on all variables that I'm getting for my SQL statement

    definitely not.
    this function have to be used to format SQL strings only

    do I have to use it when I'm doing select statements also or just on insert update and delete?

    ANY SQL statement. But again, not "using mysqli_real_escape_string" but formatting your literals completely and properly

    Also what other security would you recommend

    regarding SQL security - you have to properly format not only strings but literals of any kind. And each require distinct set of formatting rules

    链接地址: http://www.djcxy.com/p/16726.html

    上一篇: 这是一种将表单数据插入MySQL数据库的安全方法吗？

    下一篇: PHP的MySQLI防止SQL注入