I'm using prepared statements and MySQLi with my queries to protect against injection attacks. Would prepared statements remove the need for mysql_real_escape_string entirely? Is there anything else I should consider when securing my site?
views:
77answers:
2
+4
A:
As long as you're using the prepared statements correctly they will. You have to make sure you're binding all the external variables and not putting them directly in the query.
For example
$stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=" . $name);
This statement is being prepared, but it doesn't use one of the bind methods so it does no good. It is still vulnerable to SQL injection.
To fix that make sure to bind everything...
$stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
$stmt->bind_param("s", $city);
Galen
2010-09-19 16:41:40
why is this word - "external"? Shouldn't be "internal" variables binded as well? why such a segregation?
Col. Shrapnel
2010-09-19 17:11:15
External as in variables outside of your control. For instance if you defined a table name in your config file(define('TABLE', 'table')). The only way to be sql injected with that variable is to have your server compromised and that defined variable edited.
Galen
2010-09-19 18:00:11
You actually can't use table names as binded variabled in PDO for instance. You would have to hardcode it into the query or store it in a variable.
Galen
2010-09-19 18:01:02
`External as in variables outside of your control` - so what? Does it mean that parameterizing should be used only for external variables?
Col. Shrapnel
2010-09-19 20:28:28
No, thats not what it means.
Galen
2010-09-19 21:06:47
+1
A:
I'm using prepared statements and MySQLi with my queries to protect against injection attacks.
Don't do it. Don't use prepared statements to protect from anything. It is not what prepared statements are for. It is just to make your queries syntactically correct. And, as a side effect, a syntactically correct query is invulnerable to any attack too.
So, just use it to put data into query.
Would prepared statements remove the need for mysql_real_escape_string entirely?
That's wrong from the mysql_real_escape_string side. this function do not protect you from anything. If you just apply this function to your data, it wouldn't make it "safe". this function works for the quoted strings only.
While yes, using prepared statements makes this function obsolete, as well as other plain SQL assembling rules. It actually does the job you think mysql_real_escape_string do. It does actually make any data safe (for SQL).
Is there anything else I should consider when securing my site?
Sure.
Not to talk of whole city - it's another and way too broad question, but of SQL query again:
Prepared statements makes only data safe.
Thus, you have to take care of dynamical non-data parts of the query, such as field names, operators, etc. Prepared statements won't help you with them.
Col. Shrapnel
2010-09-19 17:04:54