2

I have been fussing around with my PHP code and SQL statements and while I do know that one could use prepared statements when dealing with this, I still wonder how a SQL injection could be performed here.

$name = mysql_real_escape_string(htmlspecialchars($_POST["Name"]));
$age = (int) mysql_real_escape_string(htmlspecialchars($_POST["Age"]));
$amount = (int) mysql_real_escape_string($_POST["Amount"]);

$sql = "insert into nice_table set
                    name='{$name}',
                    age='{$age}',
                    amount='{$amount}'";


$db->sql_query($sql);

I don't know a lot about all different methods when performing a SQL injection, but all the stuff I've looked up passes just fine through this without any database errors. Would it actually be safe to use this instead of the classic prepared statements?

What would be passed right through, for example? I must be missing something, because it can't be this simple and still hold as tight as prepared statements, right?

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
Em Bq
  • 55
  • 7
  • `htmlspecialchars` is not for preventing SQL injection. What you should probably do is learn to use prepared statements with bind variables. Then you wouldn't have to wonder about "Am I doing this right?" – Andy Lester Jan 12 '14 at 06:06
  • The casting of the two integers means that you do not need anything else on those lines - SQL injection is not possible via those routes if you have converted them to integers. As for `$name`, `htmlspecialchars` is not required either. If you are using it to protect from XSS, it is normal to use this when rendering the name, not when storing it. – halfer Jan 12 '14 at 08:22

3 Answers3

3

mysql_real_escape_string ALONE can't prevent all type of SQL Injection.

Whenever you need escaping, you need it despite of "security", but just because it is required by SQL syntax. And where you don't need it, escaping won't help you even a bit.

The usage of this function is simple: when you have to use a quoted string in the query, you have to escape it's contents. Not because of some imaginary "malicious users", but merely to escape these quotes that were used to delimit a string. This is extremely simple rule, yet extremely mistaken by PHP folks.

This is just syntax related function, not security related.

Depending on this function in security matters, believing that it will "secure your database against malicious users" WILL lead you to injection.

A conclusion that you can make yourself: No, this function is not enough.

Prepared statements is not a silver bullet too. It covers your back for only half of possible cases. See the important addition I made to the famous question for the details

Community
  • 1
  • 1
Sriraman
  • 7,658
  • 4
  • 40
  • 60
  • If the `mysql_real_escape_string` is not enough to prevent injection, can you clarify your answer to explain what else is required? – halfer Jan 12 '14 at 08:18
  • Usage of prepare statement can avoid SQL Injection in many cases. If you want to avoid all type of SQL injection refer http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/8255054#8255054 – Sriraman Jan 12 '14 at 08:21
  • So, in saying "mysql_real_escape_string ALONE can't prevent all type of SQL Injection", the point you were making was that the developer needs to check dynamically built SQL to ensure unintended queries are not created. That's true enough, but I don't want the OP to understand from your answer that non-parameterised solutions are vulnerable - if they are otherwise well-written, they are still safe. – halfer Jan 12 '14 at 08:30
  • ya. you are correct.. I said generally. In some scenarios just using mysql_real_escape_string alone solve all the problems. In some scenarios, It may fail. So, We should act according to our scenario. – Sriraman Jan 12 '14 at 08:35
2

mysql_ functions are deprecated. Preffer mysqli or pdo classes.

And AFAIK, it is possible to use special characters to avoid mysql_real_escape_string.

I would preffer to use prepared statements and validation. You probably wants only alfanumerics and dot to be possible inputs on name. That would help too :P

jgabriel
  • 555
  • 2
  • 12
1

No, you are using mysql_real_escape_string() properly, so this will be safe.

For the latter two variables, you could also do 

$age = intval($_POST["Age"]);
$amount = intval($_POST["Amount"]);

and that will be just as safe. Intval always returns an integer (0 on error), so it's impossible to contain any not-mysql-safe characters.

Phil
  • 818
  • 5
  • 14