When do i have to use anti mysql injection?

[Eronisch]

Hi,

I'm currently working on my website and i am bit afraid my website is vulnerable for sql injections.
I am not sure when i have to protect it by using a magic escape, mysql string escape etc etc.

I'm a beginner and i still need to learn a lot.

Some of my codes:

// Query
$test = mysql_query("SELECT roomvisits,achievementscore,onlinetime,respect FROM `user_stats` WHERE id = '".$_SESSION['user']['id']."'") or die(mysql_error());

// Results in variable
$row = mysql_fetch_row($test);

// Show first result
echo $row[0]; // room visits

<?php

    $sql = mysql_query("SELECT * FROM user_badges WHERE user_id = '".$_SESSION['user']['id']."' ORDER BY badge_id ASC LIMIT 10") or die(mysql_error());
    $count = mysql_num_rows($sql);
?>


    <?php
    if($count == 0){
        echo "You don't have any badges yet.";
    }else{
    ?>
    
<?php
    while($badgerow = mysql_fetch_assoc($sql)){
echo "   <img src='http://new.diaxa.eu/r63/c_images/badges/".$badgerow['badge_id'].".gif'/>   "; 


}
}
    ?>

Do i have to protect this or is this safe?
Could you tell me when i have to filter query's?

 

[Joopie]

$_SESSION is stored server side and there is now way the user can change the value. However, when you set a session with a user value by a cookie, get or post without filtering it, then it's vulnerable.

Just make sure you filter every cookie, get and post before you set an session with it.

Wrong:

<?php
$_SESSION['test'] = $_GET['test'];

mysql_query('some query with ' . $_SESSION['test']);
?>

Good:

<?php
$_SESSION['test'] = mysql_real_escape_string($_GET['test']);

mysql_query('some query with ' . $_SESSION['test']);?>

Good (only when the values are filtered from an previous query):

<?php
$_SESSION['test'] = mysql_result($result, 0);

mysql_query('some query with ' . $_SESSION['test']);?>

And take a look on MySQLi prepare statements. It makes your life easier.

 

[TimeBomb]

Your code appears to be safe from SQL injection, at first glance. Though it'll error out somewhere along the line if the SQL query doesn't return the expected result, i.e. there was a problem with it. If you're not already doing so, you may want to ensure that the session user's id exists and is a valid integer.

You need to protect from SQL injection whenever there is user input going into the SQL query. You also need to be very careful when using input not from the user in SQL queries, and ensure that that input does not have any characters that could mess up the SQL query or accidentally make it do/return something else.
Or you could just use parametrized queries(the best solution against mysql injection in many people's opinion) for any input, whether or not it's coming from the user. This is what I usually do.

A parametrized query, or

You must be registered to see links

, is essentially where you set spaces for parameters in the SQL query string and then separately set those parameters to whatever you want. (...That sentence was poorly worded; my apologies, it's 4:30 AM and I have yet to sleep. Just read the wiki page.) It's like a safe "replace" function. It handles your parameters as what they are - data, rather than as part of the SQL query syntax.

The MySQL library you're using will be deprecated in the near future. It is also not capable of parametrized queries.

You must be registered to see links

is most similar to the MySQL library, though it also has an object oriented interface.

You must be registered to see links

is your second alternative. It's API is a bit different from mysql/mysqli, offers only an object oriented solution, and works with multiple databases - not just MySQL. It has other advantages and arguable disadvantages as well, so read up on it.

 

[TheAJ]

Since you are using PHP, first of all, start using mysqli. That way, you can create

You must be registered to see links

, which will take care of most exploits.

Since you are using PHP, first of all, start using mysqli. That way, you can create

You must be registered to see links

, which will take care of most exploits.

Further more, prepared statements allow you to execute the same code without the MySQL server having to reparse the query, so the performance increase is noticable

 

[s-p-n]

Further more, prepared statements allow you to execute the same code without the MySQL server having to reparse the query, so the performance increase is noticable

It is important not to misinterpret AJ's statement above.

If you have lots of queries that only get parsed once then PHP will send the query and the data separately- so many unique queries will be slower to execute because there is additional type-checking, parsing, and networking.. However, if you prepare a query that will be executed 1000s of times, then AJ is dead on the money- the query is parsed and waiting for type-safe data and with the absence of type-checking and query parsing, intuitively the sequence of query executions should be much faster.

It's important to design applications with this in mind. Also, since the data becomes type-safe in the process, you are by dafacto protecting your application from MySQL injections and unsafe types. For that reason alone, you should use prepared statements.

When to use prepared statements? Anytime you are sending variable data to the database. Any user input, and data from 3rd parties such as Facebook Twitter or Google. In general application-generated data is safe (such as a custom algorithm for a hashed password), but if the outcome is not guaranteed type-safe or if it relies directly on a 3rd party in order to generate data, or if you are not sure- you should use prepared statements.

When not to use prepared statements? Anytime you are sending a static (never changing) query to the database. For example, if you hardcode a query with all of the values set like this: - - then you do not need to (and should not) be using prepared statements.

$mysqli->query('SHOW TABLES');

Notice the above query will never change- there are 0 variables in PHP that can change the result of this query. Thus, it doesn't need any protection.