MySQL SQL SQL注入防止

一、SQL注入例

$unsafe_variable = $_POST['user_input']; 
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

POSTの内容が次の場合:

value'); DROP TABLE table;--

以上のSQLクエリ文全体が次のようになります.

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

二、SQL注入防止措置
1.Use prepared statements and parameterized queries.
SQL文とクエリのパラメータは、それぞれデータベース・サーバに送信されて解析されます.この方式は,(1)PDO(PHP data object)を用いた2つの実装がある.

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute(array('name' => $name));
foreach ($stmt as $row) {
    // do something with $row
}

(2)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
}

2.クエリ文のエスケープ(最も一般的な方法)

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);
mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

Warning: As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Please use mysqli extension and mysqli::escape_string function instead 

$mysqli = new mysqli("server", "username", "password", "database_name");
// TODO - Check that connection was successful.
$unsafe_variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// TODO check that $stmt creation succeeded
// "s" means the database expects a string
$stmt->bind_param("s", $unsafe_variable);
$stmt->execute();
$stmt->close();
$mysqli->close();

3.導入パラメータの制限

$orders  = array("name","price","qty"); //field names
$key     = array_search($_GET['sort'],$orders)); // see if we have such a name
$orderby = $orders[$key]; //if not, first one will be set automatically. smart enuf :)
$query   = "SELECT * FROM `table` ORDER BY $orderby"; //value is safe

4.導入パラメータの符号化

SELECT password FROM users WHERE name = 'root'            --    
SELECT password FROM users WHERE name = 0x726f6f74        --    
SELECT password FROM users WHERE name = UNHEX('726f6f74') --    

set @INPUT =  hex("%  %");
select * from login where reset_passwd_question like unhex(@INPUT) ;

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways: 0x prefix can only be used on data columns such as char, varchar, text, block, binary, etc. Also its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error. UNHEX() works on any column; you do not have to worry about the empty string.
5.MySQLストアド・プロシージャの使用
その他:入力パラメータの検証
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php
http://stackoverflow.com/questions/18026088/pdo-sends-raw-query-to-mysql-while-mysqli-sends-prepared-query-both-produce-the