Help me stop the user from entering harmful codes.

Question

Hi there,

this question had been evolving in my mind, how do i totally stop the users from entering some crazy SQL injections. isn't mysql_real_escape_string powerful enough to stop it? i followed some guidelines though there were some users in here who criticized my code and gave me thumbs down for the security. i was unable to understand the reason behind it. though i am not using $_GET, the only user input is through commenting system. i just want to make sure i am not going wrong. here is my sample code.

$name = htmlspecialchars(strip_tags(mysql_real_escape_string($_POST['com_name'])));

I have used the same for some 5 fields. what is your take on my above code?

Answer 1

The idea is not to stop the users from entering it, but to make sure you enter them in the database query safely. This means using mysql_real_escape_string or using parameterized queries with mysqli:

/* Prepare an insert statement */
$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
$stmt = mysqli_prepare($link, $query);

mysqli_stmt_bind_param($stmt, "sss", $val1, $val2, $val3);

You also need to make sure your properly html encode fields you read from the database, to avoid that the user is able to inject html (and also javascript)

Answer 2

If you are inserting user input into the database than mysql_real_escape_string will suffice. Better yet, make use of prepared statements - PDO or MySQLi.

If you are simply displaying user data on the page you should make use of htmlspecialchars() or htmlentities()

As for the code you have posted. Lets break it down.

• htmlspecialchars - converts html characters into respective entities.
• strip_tags - Strips HTML and PHP tags from the code
• mysql_real_escape_string - Escapes characters prior to DB entry.

So, as you can see, the first two functions you are using seem rather counter productive. You are covnerting HTML chars into entities, but you are also stripping tags.

To make sure you are securing user input before DB entry mysql_real_escape_string will suffice. Or as I mentioned earlier, make use of prepared statements.

Answer 3

It's possible your call to mysql_real_escape_string will fail if a connection to your database cannot be found or created.

I tend to put such calls as deep down as possible right before I actually perform a query. It may kill my performance a little, but I don't have to worry about missing a parameter higher up (with respect to sql injection), and I know I always have a valid connection to the database at that point.

The first thing I also do on all user input is run it through filter_var with FILTER_SANITIZE_SPECIAL_CHARS before doing anything else with it.

Answer 4

No, your code isn't quite secure and even less sensible.

Here is a complete answer about SQL injections I posted before
In short, mysql_real_escape_string itself doesn't protect anything. It works only if you put your data in quotes.

As for the htmlspecialchars/strip_tags, both has nothing to do with SQL at all, it's HTML protection, not SQL. And using both of them is redundant. Just one of them is enough. I'd prefer htmlspecialchars.