mysql_real_escape_string

[shadowayex]

I have a class that represents fields from a database. It implements arrayaccess. The class is passed an id on construction, and it's properties are filled with results from a query. The properties than then be changed through arrayaccess and then a save method can be called to commit changes to the database. As of right now, I call mysql_real_escape_string on all of the properties in the save method before doing the database update. I was wondering if it was safe and/or proper to, instead, call mysql_real_escape_string on values coming in when changes are made via arrayaccess. I figured that would make for less function calls in the case that some properties remain unchanged between calls to save().Along the same lines, is data that comes out of a database safe to put back in without mysql_real_escape_string? Like, if I was testing mysql injections, and I used mysql_real_escape_string on one that got saved to the database, if I query it back out, would I need to do mysql_real_string_escape on it to put it back in?

[[dx]]

I guess not. Couse you can only do SQL INjection, not SQL OUTjection If you understand me.While writing to book, you can write anything you want, but while reading, you read what is already written.

[boen_robot]

It's best you do the mysql_real_escape_string() at the time of the actual SQL query. Since you're scheduling the actual updates to occur at another point (descruction? method call?), then it's best you do the escaping there. That will reduce the amount of required escapes to be no more than N (N = amount of columns). Doing it on the data modification itself (via ArrayAccess or other approaches) means you could have more than N escapes if a single data piece is updated more than once within a single script.If you can afford to waste some extra memory, you could keep copies of the original data, or keep flags that say whether the data is modified or not, and only update the parts that have a flag. Either way, if you have a value that is part of the query, it must, at some point, be escaped.

[shadowayex]

The updating is done via method call.It's looking like I should do the escape in the save method. I'm toying with the idea of making a dynamic query string, where I could keep a boolean array to keep track of what's been updated and what hasn't, and then only escape and update the fields that have actually been updated. I don't know if this is a worthwhile approach, but it's an idea.

[justsomeguy]

Along the same lines, is data that comes out of a database safe to put back in without mysql_real_escape_string?

No, the database saves the original data, not the escaped data. Escaping the data makes it possible for the database to know what the data is, but it doesn't change the data as far as the database is concerned.

[[dx]]

Could someone explain how does this function works?I found some soft which converts simbols č ć ž š đ and other to some number; and so.But how this works when stripping SQL Injection commands?And is it best to use it in every input query. Let's say for this forum.If we talk on topic about SQL Injection and we paste queries like WHERE user='' AND pass='' etc.Generaly, we could use SQL Injection when posting message. But if function is used, it refuses to do SQL Inject.But if we post message, is message goes through some filter or anything like that.Like adding \ before ' etc. So it will be WHERE user=\'\'Thanks

[boen_robot]

mysql_real_escape_string() adds a "\" before every character that can't legally be part of a string in a MySQL query. Note that the MySQL query itself is written as a string, but it's not a string as far as MySQL is concerned.Though there isn't exactly a formal way to say it, I like to think of strings as "MySQL string", "PHP string", "JavaScript string", "[language] string" to help me clarify what kind of language is treating said string.The PHP string

SELECT * FROM users WHERE username='boen_robot';

doesn't mean anything as far as PHP is concerned. It's just some sequence of characters.Given to MySQL, this is no longer a string, but a query, and part of this query is the MySQL string

boen_robot

Again, to MySQL, these don't have any special meaning. But there are some characters that according to MySQL can't be part of its strings. The most typical example is the ' character, which is not allowed because it's used to end the string. What happens if you have the PHP string

Deirdre's Dad

and you want to make this be a MySQL string in a query similar to the above? If you do it like

$user = "Deirdre's Dad";$sql = "SELECT * FROM users WHERE username='$user';"

the PHP string $sql becomes

SELECT * FROM users WHERE username='Deirdre's Dad';

which to MySQL is an error because from its point of view, the string is

Deirdre

and then there's some keywords "s Dad" and a new string starting with ";" and which was never finished. MySQL it doesn't know what to do with those.And yet that's actually the better scenario. What if MySQL did know what to do with those remaining things? That's what an SQL injection is.So what to do, what to do... hmm... wouldn't it be nice if there was a function that would turn ' and any other potential disasters into something that would work as intended? That's exactly the role of mysql_real_escape_string(). If you had

$user = mysql_real_escape_string("Deirdre's Dad");$sql = "SELECT * FROM users WHERE username='$user';"

the PHP string $sql will be

SELECT * FROM users WHERE username='Deirdre\'s Dad'

which to MySQL is OK, because the "\" in front of ' makes it loose its special meaning to end a string, so the string continues up tp the actual string end.

[[dx]]

Thanks what I thought. Thanks. Good tutorial if we can call it like that