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?
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