PHP SQL Injection 기초적인 예방

Preventing SQL Injection in PHP

다음 SQL 구문 이라면 

$uid = $_REQUEST[‘uid’];
SELECT * FROM Users WHERE uid = “$uid”; 

SQL 인젝션 형태

SELECT * FROM Users WHERE uid = 120 or 1=1;
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""
SELECT * FROM Users; DROP TABLE User_Feeds

1. mysql escape string 사용

// procedural method of calling
$uid= mysqli_real_escape_string($uid);

// Object method of calling
$uid= $mysqli->escape_string($uid);

<?php
    
    //  Full Sample code
    //	Creating connection and checkng for errors
    //	Error Statement
    //	Escaping the string and execution
    
    
    /* create connection */
    $mysqli = new mysqli("localhost", "username", "password", "database");

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }


    $city = "'s hyderabad";

    /* this query will fail, because we didn't escape $city */
    if (!$mysqli->query("INSERT into city (Name) VALUES ('$city')")) {
        printf("Error: %s\n", $mysqli->sqlstate);
    }

    $city = $mysqli->escape_string($city);

    /* this query with escaped $city will work */
    if ($mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
        printf("%d Row inserted.\n", $mysqli->affected_rows);
    }

    $mysqli->close();
?>

2 prepare 사용

<?php
    // Create a Connection 
    $mysqli = new mysqli("server", "username", "password", "db_name"); 
    $uname = $_POST["username"];
    //check that $stmt creation succeeded 
    // "s" means the database expects a string 
    $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)"); 
    $stmt->bind_param("s", $uname);
    $stmt->execute();
    $stmt->close();
    $mysqli->close();
?>

3 pdo prepare 사용

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

4. 위 3가지는 mysql 접속이이뤄지고 난후 동작 하거나  mysql에 종속 되어있으나 다음  펑션으로 필터할수있다 

function sqlescape($value)
{
    $search = array("\\",  "\x00", "\n",  "\r",  "'",  '"', "\x1a");
    $replace = array("\\\\","\\0","\\n", "\\r", "\'", '\"', "\\Z");

    return str_replace($search, $replace, $value);
}