2 small Q's about mysql_real_escape_string()

Hi groupies,

I have 2 small questions, which i really want to be sure about:

- does mysql_real_escape_string() prevent any kind of mysq-injection?
- can it put escaped quotes etc in the DB, or are those automatically
'converted' again?

Thanks!

Frizzle.

re: 2 small Q's about mysql_real_escape_string()

frizzle said the following on 07/11/2005 14:53:[color=blue]
> Hi groupies,
>
> I have 2 small questions, which i really want to be sure about:
>
> - does mysql_real_escape_string() prevent any kind of mysq-injection?[/color]

As long as you use it appropriately. i.e. use it on all string-based
values, and don't allow user input to directly govern query string
structure. Other data-types should be explicitly cast before being used
in a query.

[color=blue]
> - can it put escaped quotes etc in the DB, or are those automatically
> 'converted' again?[/color]

mysql_real_escape_string() takes all instances of " and converts them to
\", and all instances of \ to \\, etc.

So if your input string already contains \", then it will be converted
to \\\" before being added to the query string. Therefore, (if used in
an INSERT statement), the database will then contain \". When SELECTING
this, the result will still be \".

--
Oli

re: 2 small Q's about mysql_real_escape_string()

Thanks Oli,

I don't completely understand your answers:
If i * always * use the mysql_real_escape_string() am i safe?
And with an insert statement, does it matter what kind of quotes i use
for the query?
Like
"Select * From 'bla' "
or
'Select * From \'bla\' ' ?

Thanks.

Frizzle.

re: 2 small Q's about mysql_real_escape_string()

frizzle said the following on 07/11/2005 15:34:[color=blue]
> Thanks Oli,
>
> I don't completely understand your answers:
> If i * always * use the mysql_real_escape_string() am i safe?[/color]

Yes, if all you're doing is taking values from users and putting them
into appropriate places in an INSERT query, e.g.:

INSERT INTO table (name, age, class) VALUES ('XXX', 'YYY', 'ZZZ')

If you're allowing more complex interaction, e.g.

SELECT FROM table XXXXXXXXXX

where the user is allowed to specify the condition string, then
obviously mysql_real_escape_string() is of no use here, and you're very
much less than safe.
[color=blue]
> And with an insert statement, does it matter what kind of quotes i use
> for the query?
> Like
> "Select * From 'bla' "
> or
> 'Select * From \'bla\' ' ?
>[/color]

If you express a string in PHP code as:

$s = "This is 'some' text";

or

$s = 'This is \'some\' text';

the internal representation of $s will be:

This is 'some' text

in both cases. If you were then to do SomeFunction($s), it is this
internal representation that is used; how the string was originally
represented in code is no longer relevant.

Remember, PHP escaping is entirely separate from MySQL escaping. PHP
escaping is necessary to represent a string in PHP code, whereas MySQL
escaping is necessary to represent string-based data in a query string.

e.g. To insert:

This is 'some' text

into a MySQL database, the query string should be:

INSERT INTO table (field) VALUES ('This is \'some\' text')

and to represent that query string in literal PHP code, it should be:

"INSERT INTO table (field) VALUES ('This is \\'some\\' text')"
or:
'INSERT INTO table (field) VALUES (\'This is \\\'some\\\' text\')'
or:
"INSERT INTO table (field) VALUES ('"
. mysql_real_escape_string("This is \'some\' text")
. "')"
--
Oli

re: 2 small Q's about mysql_real_escape_string()

Oli Filth said the following on 07/11/2005 15:48:[color=blue]
> "INSERT INTO table (field) VALUES ('"
> . mysql_real_escape_string("This is \'some\' text")
> . "')"[/color]

Oops, that last one should be:

"INSERT INTO table (field) VALUES ('"
. mysql_real_escape_string("This is 'some' text")
. "')"

--
Oli

re: 2 small Q's about mysql_real_escape_string()

Hmm, great, i understand what you mean.
With the real-escape part, i'm not using
the second case at all, so there's no problem
there.

Thanks for explaining all!

Frizzle.

re: 2 small Q's about mysql_real_escape_string()

On 7 Nov 2005 06:53:32 -0800, "frizzle" <phpfrizzle@gmail.com> wrote:
[color=blue]
>I have 2 small questions, which i really want to be sure about:
>
>- does mysql_real_escape_string() prevent any kind of mysq-injection?[/color]

If you always remember to use it correctly, then it stops the class of SQL
injection attacks where values are interpreted as SQL.
[color=blue]
>- can it put escaped quotes etc in the DB, or are those automatically
> 'converted' again?[/color]

Well, that's one of its more common uses, to escape quotes in quoted strings
properly. Nothing's automatically converted, unless you have the dreaded
magic_quotes option turned on. So not sure what you're asking there.

I still maintain you're better off using a library such as ADOdb which
emulates placeholders for MySQL - not only do you not have to worry about
escaping any more, it also gets you into better habits for other databases
where using placeholders/bind variables is critical for efficiency.

http://adodb.sourceforge.net/
http://phplens.com/adodb/reference.f...s.prepare.html
--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

re: 2 small Q's about mysql_real_escape_string()

Hmm,
somehow i can't get it to work correctly:

I tested inserting the following text:
'"\ (single quote, double quote, backslash)

Via PHP:
$new_var = mysql_real_escape_string($_POST['var']);
$put_info = mysql_query("INSERT INTO `test` ( `id` , `text` )
VALUES ('', '".$new_var."');");

And via phpMyAdmin.
If i view the first data via phpMyAdmin, i see the following:
\'\"\\

If i view the text inserted via phpMyAdmin, i see
'"\ (the way it should be)

What am i still missing? :s

Greetings Frizzle.

re: 2 small Q's about mysql_real_escape_string()

On 8 Nov 2005 07:15:33 -0800, "frizzle" <phpfrizzle@gmail.com> wrote:
[color=blue]
>Hmm,
>somehow i can't get it to work correctly:
>
>I tested inserting the following text:
>'"\ (single quote, double quote, backslash)
>
>Via PHP:
> $new_var = mysql_real_escape_string($_POST['var']);
> $put_info = mysql_query("INSERT INTO `test` ( `id` , `text` )
>VALUES ('', '".$new_var."');");[/color]

Print out all the values involved to the browser so you can actually see what
is happening.

In my previous reply that you haven't quoted, I said:
[color=blue][color=green]
>>Nothing's automatically converted, unless you have the dreaded
>>magic_quotes option turned on.[/color][/color]

So, do you have magic_quotes turned on? If you don't know, use Google to find
out what it is. There is a chapter in the PHP manual about it.
--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

re: 2 small Q's about mysql_real_escape_string()

Andy Hassall wrote:[color=blue]
> On 8 Nov 2005 07:15:33 -0800, "frizzle" <phpfrizzle@gmail.com> wrote:
>[color=green]
> >Hmm,
> >somehow i can't get it to work correctly:
> >
> >I tested inserting the following text:
> >'"\ (single quote, double quote, backslash)
> >
> >Via PHP:
> > $new_var = mysql_real_escape_string($_POST['var']);
> > $put_info = mysql_query("INSERT INTO `test` ( `id` , `text` )
> >VALUES ('', '".$new_var."');");[/color]
>
> Print out all the values involved to the browser so you can actually see what
> is happening.
>
> In my previous reply that you haven't quoted, I said:
>[color=green][color=darkred]
> >>Nothing's automatically converted, unless you have the dreaded
> >>magic_quotes option turned on.[/color][/color]
>
> So, do you have magic_quotes turned on? If you don't know, use Google to find
> out what it is. There is a chapter in the PHP manual about it.
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool[/color]

Sorry for not quoting, i only recently discovered where this option is
in
Google-groups...
Anyway, i checked the server's settings, and concerning magic quotes,
it
said the following:

magic_quotes_gpc On On
magic_quotes_runtime Off Off
magic_quotes_sybase Off Off

Thanks!

Frizzle.