CODEWITHSUNDEEP
phpsqlsql-injection
Iladarsda
Iladarsda

Reputation: 10692

PHP code vulnerable to SQL injection: how to work with `mysql_real_escape_string()`?

As I was learning from couple of years old tutorials I have end up with following code which IS vulnerable for SQL injections.

Can someone explain to me how to work with mysql_real_escape_string()? Is this currently bulletproof method? 

//Function to sanitize values received from the form. Prevents SQL injection
    function clean($str) {
    $str = @trim($str);
    if(get_magic_quotes_gpc()) {
        $str = stripslashes($str);
    }
    return mysql_real_escape_string($str);
}

//Sanitize the POST values
$usr = $_SESSION['usr'];
$live = (isset($_POST['live']))?1:0;
$created = date("F j, Y, g:i a",time()+60*60);
$title= clean($_POST['title']);
$content = clean($_POST['content']);

//Create INSERT query
$qry = "INSERT INTO news( usr, live, created, title, content) VALUES( '$usr', '$live', '$created', '$title', '$content') ";
$result = @mysql_query($qry);

Upvotes: 1

Views: 356

Answers (7)

Nasaralla
Nasaralla

Reputation: 1839

I would do something like

sprintf(" SELECT * from table_name WHERE value = '%s'", mysql_escape_string("$var_value"));

The %s from the sprintf() function indicates that the argument is treated as and presented as a string.

If an attack is made such as the one from the previous example the query sent will be: view source print?

SELECT * FROM `members` WHERE username='john' AND password='\' OR \'\'=\''

and will return an empty result set. (source)

Upvotes: 1

fdaines
fdaines

Reputation: 1246

you can use mysql_real_escape_string, to escape dangerous characters...

$sanitized_query = sprintf("SELECT * FROM table WHERE field='%s', mysql_real_escape_string($value));

Upvotes: 1

Tomas
Tomas

Reputation: 59565

Yes, it is. In this case.

Note there is nothing like "universal sanitization". Let's call it just quoting, because that's what its all about.

When quoting, you always quote text for some particular output, like:

  1. string value for mysql query
  2. like expression for mysql query
  3. html code
  4. json
  5. mysql regular expression
  6. php regular expression

For each case, you need different quoting, because each usage is present within different syntax context. This also implies that the quoting shouldn't be made at the input into PHP, but at the particular output! Which is the reason why features like magic_quotes_gpc are broken (I recommend to keep it switched off).

So, what methods would one use for quoting in these particular cases? (Feel free to correct me, there might be more modern methods, but these are working for me)

  1. mysql_real_escape_string($str)
  2. mysql_real_escape_string(addcslashes($str, "%_"))
  3. htmlspecialchars($str)
  4. json_encode() - only for utf8! I use my function for iso-8859-2
  5. mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case because backslash would be escaped two times!
  6. preg_quote()

Upvotes: 1

J0HN
J0HN

Reputation: 26951

Everything you append to query string you should escape with mysql_real_escape_string. It will prevent most of SQL injections. But better use prepared statements

Example: 

$pdo = new PDO($dsn,$username,$pass);
$stmt = $pdo->prepare("select * from Table where id=?");
if ($stmt->execute(array(1))){
    $content = $stmt->fetchAll();
}

See PDO::__construct for reference

Upvotes: 2

Heinzi
Heinzi

Reputation: 6091

I think your method is sql injection save.

Upvotes: 1

evasilchenko
evasilchenko

Reputation: 1870

Nothing is bulletproof when it comes to hacking; having said that, yes.. mysql_real_escape_string() prevents SQL injection attacks.

Upvotes: 1

genesis
genesis

Reputation: 50982

Yes, this one is fine. You can use prepared statements, though

Do not use @ to hide all errors!

Upvotes: 2

Related Questions