mysql_real_escape_string needed to used twice

[behz4d]

Hello everybody,

 

I'm inserting a $keyword to my database, and for preventing SQL Injections i'm using mysql_real_escape_string function to escape dangerous characters, but the problem is :

When i use  mysql_real_escape_string ONCE on $keywordm it's not working, I MUST USE mysql_real_escape_string TWICE !

see this :

 

$keywords = "this is a ' test !";
$keywords = mysql_real_escape_string($keywords);
mysql_query("INSERT INTO table_name (keyword) VALUES ('$keywords')");

 

the inserted keyword is : this is a ' test !

but when i try mysql_real_escape_string TWICE, then it's fine :

 

$keywords = "this is a ' test !";
$keywords = mysql_real_escape_string($keywords);
$keywords = mysql_real_escape_string($keywords);
mysql_query("INSERT INTO table_name (keyword) VALUES ('$keywords')");

the inserted keyword is : this is a \' test !

 

what is the problem !?

why I need to use this function twice to make it works ? 

 

I would appreciate any kind of help

Regards

[Pikachu2000]

The backslashes aren't supposed to be inserted into the database. They just let it know the following character is to be inserted, and is not interpreted as a special character. Escaping the string once is more than sufficient . . . Try this:

 

$keyword = "this is a ' test !";
$keywords = mysql_real_escape_string($keywords);
echo $keywords;

[behz4d]

thanks for your quick reply !

you were right, but can i ask about this function ?

in general, what are the dangerous chars which we should prevent to be inserted into the database ?

 

what does this function do ? which characters this function filters ?

 

and at last, you mean, if we have such string in our database, is it OK :

" this i a ' test "

 

another thing, how i can see if the function is working or not ?

plz give me a string which changes after using mysql_real_escape_string and inserted into db...

i mean this should affect on strings which we insert into db, but when i use this function on this string : " this i a ' test ", the inserted string is all the same, so what's the point ?

 

sorry for being noob 

[Pikachu2000]

A string sitting in a database isn't going to do anything harmful in a million years. There are no characters that are dangerous to insert into a database, per se. Where the problem lies is if a malicious user is able to manipulate your query string to make it do things you don't intend for it to do. If you have a field in a form, and the query uses that value to update a record, you'd expect it to update that record only. If that query was suppose to read:

UPDATE table SET field = 'new value' WHERE name = 'name_from_form'

 

What would happen if someone typed this?

Bob' OR 1=1

 

Suddenly the query reads UPDATE table SET field = 'new value' WHERE name = 'Bob' OR 1=1', and that now matches every record in the table.

 

Escaping tells MySQL to treat the single quote in the string as just a character to be inserted like any other character, instead of a character that has any significance to the operation.

 

There's a ton of information to be found by Googling 'preventing sql injection', but generally speaking, string type data should be escaped with the appropriate function, for MySQL it's mysql_real_escape_string, and be enclosed in quotes in the query string. Numeric data should be validated, cast as the correct type (integer, float, etc.), and left unquoted in the query string.

[behz4d]

Thank you Pikachu2000

Now i'm completely cleared, I though this function is supposed to change or replace some strings which we want to insert into the database, but i was wrong.

 

Thank you in advance

 

[Pikachu2000]

Well, technically it does change them. But when the string is inserted, MySQL strips the escaping backslashes out of the string so it can be stored in its proper format. If you echo the string after using mysql_real_escape_string(), you should see the backslashes. Once inserted into the database, you should not.

 

It's important to understand how this works, so if you have any other questions please ask.