Secure against SQL Injection - PDO, mysqli

Question

Possible Duplicate:
Best way to prevent SQL Injection in PHP

I just found that my website is vunerable.

Since it's connected to a DB and have functions like: Register, Change Password, Notices, etc... and SUPOSING it's fully vulnerable.

What should I look for into the code in order to start making it safe?

I mean, I did some researches and everywhere, everyone says different things about security.

"Use PDO."

"Use mysql_real_escape_string."

"Use addslashes."

What exactly should I look for??

"$_POST" and "$_GET" variables??
"$_SESSION" variables?

SQL querys?

$sql = "select * from user";
$sql = "update user set user="new_user_name";
$sql = "insert into user (user) values ('userid')";

What should I do in each case? Please, help me to know what and where I must go.

Thank you.

Answer 1

Following are the points to be considered for making safe php application.

1. USE PDO or mysqli
2. Never trust any inputs. Consider every variable viz $_POST, $_GET, $_COOKIE, $_SESSION, $_SERVER as if they were tainted. Use appropriate filtering measure for these variables.
3. To avoid XSS attack use php’s builtin functions htmlentities, strip_tags, etc while inserting the user input data into the database.
4. Disable Register Globals in PHP.INI
5. Disable “allow_url_fopen” in PHP.INI
6. Don’t allow user to input more data than required. Validate input to allow max number of characters. Also validate each field for relevant datatypes.
7. Disable error reporting after Development period. It might give information about database that’ll be useful to hackers.
8. Use one time token while posting a form. If token exist and matches the form post is valid otherwise invalid.
9. Use parametrized database queries
10. Use stored procedures

You can google for each point for more details. HOpe this helps

Answer 2

What you should look for: Any data send from the client/user. Sanitize/escape this data.

PDO can sanitize queries (using PDO::prepare) and supports multiple SQL systems.

For MySQL, use MySQLi. mysqli_real_escape_string is the function to use for sanitizing data if you are using MySQL.

Answer 3

None of the SQL queries you provided are actually vulnerable to SQL injection.

SQL injection vulnerabilities happen because SQL input is not properly escaped.

For example:

$sql = "select * from users where user_id ="  . $_GET['user_id'];

Consider if I passed in the following:

http://some_server.com/some_page.php?user_id=123%20or%201=1

The query when executed would end up being:

select * from users where user_id = 123 or 1=1

To fix this, use parameterized queries:

$query = "select * from users where user_id = ?"

When you bind the user_id value to the query, the data access layer will escape the input string properly and the following would be executed:

select * from users where user_id = '123 or 1=1' which would not return any rows, preventing the injection

If using PHP and the mysql extension:

$sql = "select * from users where user_id = '" . mysql_real_escape_string($_GET['user_id']) . "'";

Keep in mind you need to escape ALL input that is going into a SQL query:

$sql = "select id_column from some_table where id = 1";
$stmt = mysqli_query($conn, $sql);
if($stmt === false) die(mysqli_error($conn) . "\n");
while($row = mysqli_fetch_assoc($conn, $stmt) {
    $sql = "update some_other_table set some_value = 'new value' where some_column = '" . mysqli_real_escape_string($conn, $row['id_column']) . "'";
    ....
}

This is because values you select from the database might include characters that are not safe for execution in a SQL statement, like the name "O'Hara" or example. }

Answer 4

I've been using PDO.

An example for that in your case:

<?php
   $stmt = $dbh->prepare("insert into user (user) values (?)");
   $stmt->bindParam(1, $name);
   $name = 'ValueHere';
   $stmt->execute();
?>