+2 votes
1.1k views
in Programming by (2.1k points)

Using this:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO table (column) 
VALUES ('" . $unsafe_variable . "')");

That's because the user can input something like

 value'); DROP TABLE table;--, and the query becomes:

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

What can be done to prevent this from happening?

closed
by
Prepared statements alone won't fully prevent SQL Injection. You have to paramterize your queries too and use them both in conjunction.

1 Answer

+2 votes
by Expert (5.9k points)
selected by
 
Best answer
  1. Using PDO:

    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
    
    $stmt->execute(array('name' => $name));
    
    foreach ($stmt as $row) {
        // do something with $row
    }
  2. Using 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
    }
by (2.1k points)
Thanks duke for the answer.
by Expert (5.9k points)
+1 for good explanation in PDO and mysqli.
by Expert (5.9k points)
Thanks Himanshu :)
by
Either prepared statements or create a custom class to filter out all SQL statements based on user role
by
Use prepared statements and parameterized queries if you are using PDO.
So something like:

$query = $db->prepare("SELECT * FROM users WHERE user_id = ?");

$query->execute(array($_SESSION['user_id']));

This way you never pass values directly into the query itself you always parameterize it with a '?' which serves as a placeholder for the real value.
by
PDO and php mysqli placeholders is a best way to avoid problems, also, as a blazing fast workaround for MS SQL request values in Perl you could use a simple replace s/'/''/g for example:

$string = "Star'craft";
$string =~ s/'/''/g;
$req = "SELECT * FROM BestGames WHERE name = '".$string."'";

for sure replacement should be wrapped to some sub and used like

$string = "Star'craft";
$req = "SELECT * FROM BestGames WHERE name = '".&prepValMsSQL($string)."'";
by
PDO with parameters does the job. Remember to protect against XSS as well.
by
for prevention sql injection you can use mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier = NULL ] )
by
If user input is added without modification into an SQL query, then the application becomes vulnerable to SQL injection. The best way to defend this is to use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters making it impossible for attacker to inject malicious SQL.

Not a Member yet?

Ask to Folks Login

My Account

Your feedback is highly appreciated