Hey there! any question in your mind? Ask It Now!.

Popular Categories





Best way to prevent SQL injection in PHP?

+2 votes
905 views
asked in Programming by monika (2,040 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?

commented by anonymous
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
answered by duke Expert (5,813 points)
selected by monika
 
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
    }
commented by Tom Martin
PDO with parameters does the job. Remember to protect against XSS as well.
commented by Ankit Sakhareliya
for prevention sql injection you can use mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier = NULL ] )
commented by Hemang Rindani
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.

Related Questions

+8 votes
2 answers 139 views
+12 votes
1 answer 136 views
+8 votes
1 answer 130 views
+4 votes
5 answers 770 views
0 votes
1 answer 9,923 views
+2 votes
3 answers 314 views
asked in Programming by jatin Expert (3,823 points)
+1 vote
2 answers 5,845 views
+2 votes
1 answer 177 views
+4 votes
1 answer 662 views

Not a Member yet?

Ask to Folks Login

My Account
686 Folks are online
6 members and 680 guest online
Your feedback is highly appreciated