d3v4 — 2011-04-08T12:49:36-04:00 — #1
I always use mysqli_real_escape_string when I insert data through a form, but I was wondering if it makes any sense to insert data with escaped characters when I insert data directly through PhpMyAdmin (when I first populate the database). Is it important to use backslashes in this case too?
scallioxtx — 2011-04-08T13:04:04-04:00 — #2
Without looking at the source I can't be entirely sure, but my guess would be PHPMyAdmin executes mysql_real_escape_string (or similar) itself before it queries.
Why don't you try and see what happens?
d3v4 — 2011-04-08T13:29:43-04:00 — #3
I've tried inserting "it's" through PhpMyAdmin and the apostrophe wasn't backslashed.
scallioxtx — 2011-04-08T17:59:17-04:00 — #4
So that's good then! Problem solved
d3v4 — 2011-04-08T19:16:58-04:00 — #5
That's not what I meant I was talking from a security point of view.
Let me explain better: as far as I understand, using mysqli_real_escape_string (for example when a user enters data in a form) helps prevent attacks like SQL injection by escaping characters like apostrophes which can be dangerous. My question is: if I insert data directly through PhpMyAdmin, which means that I have direct access to the database, does it have any sense to escape characters?
ibazz — 2011-04-08T20:41:10-04:00 — #6
are you going to try to deliberatley compromise your db? If not, then you 'could' avoid the necessity to escape everything. However, if you are concerned with the possibility of someone else accessing your phpMyAdmin*, then I am not sure how you would secure it given, as you say, that it doesn't escape anything.
(*or any other db admin tool).
That said, I am not anything like the expert that others arond here are.
ibazz — 2011-04-08T20:43:11-04:00 — #7
If such security is a real concern you could, perhaps, change the login privileges each time you want to login to your db admin tool.
felgall — 2011-04-08T22:03:35-04:00 — #8
mysqli_real_escape_string has NOTHING WHATEVER to do with security. Its function is to prevent the data and the query getting mixed up so that the server doesn't know which is which.
You should validate your data when you first read it to ensure that the field content is valid for what the field is allowed to contain - as a side effect that prevents injection by not allowing the weird strings required for injection to be entered into fields where they don't make sense in the first place.
Only where legitimate content could accidentally result in crashing the query does mysqli_real_escape_string play a part and then only if you jumble the sql and data together instead of using prepare/bind.
For example: you wouldn't allow apostrophes in a username and so the username anything' OR 'x'='x should fail validation for that field long before it gets anywhere near the SQL.
d3v4 — 2011-04-09T08:32:00-04:00 — #9
So, even if you validate your data before inserting it into the database, it's always better to use mysqli_real_escape_string anyways?
And so I can avoid putting backslashes when I insert entries into the db, if I understand it right?
d3v4 — 2011-04-09T08:33:31-04:00 — #10
No, I'm not trying to damage anything xD I was just thinking about this theoretically
felgall — 2011-04-09T18:13:19-04:00 — #11
If you use prepare/bind then you don't need mysqli_real_escape_string at all since the SQL goes in the prepare statement and the data goes in the bind statement and there is no possibility whatever of them getting mixed up.
If you jumble the SQL and data together in once call then you must use mysqli_real_escape_string on any of the data which can validly contain a character that might be misinterpreted as part of the SQL rather than as part of the data - for example any field that is allowed to contain an apostrophe such as a surname field. You don't need it on any of the other data fields that are not allowed to contain any of the characters that mysqli_real_escape_string escapes as on those fields calling it will do nothing whatever (and so it doesn't do any harm to have it there either since doing nothing is the same whether it is there or not).