2 small Q's about mysql_real_escape_string()

[frizzle]

Hi groupies,

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

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

Thanks!

Frizzle.

[Oli Filth]

Re: 2 small Q's about mysql_real_esca pe_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_esca pe_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_esca pe_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

[frizzle]

Re: 2 small Q's about mysql_real_esca pe_string()

Thanks Oli,

I don't completely understand your answers:
If i * always * use the mysql_real_esca pe_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.

[Oli Filth]

Re: 2 small Q's about mysql_real_esca pe_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_esca pe_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_esca pe_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_esca pe_string("This is \'some\' text")
. "')"
--
Oli

[Oli Filth]

Re: 2 small Q's about mysql_real_esca pe_string()

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

Oops, that last one should be:

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

--
Oli

[frizzle]

Re: 2 small Q's about mysql_real_esca pe_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.

[Andy Hassall]

Re: 2 small Q's about mysql_real_esca pe_string()

On 7 Nov 2005 06:53:32 -0800, "frizzle" <phpfrizzle@gma il.com> wrote:
[color=blue]
>I have 2 small questions, which i really want to be sure about:
>
>- does mysql_real_esca pe_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.functions.prepare.html

--
Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[frizzle]

Re: 2 small Q's about mysql_real_esca pe_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_esca pe_string($_POS T['var']);
$put_info = mysql_query("IN SERT 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.

[Andy Hassall]

Re: 2 small Q's about mysql_real_esca pe_string()

On 8 Nov 2005 07:15:33 -0800, "frizzle" <phpfrizzle@gma il.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_esca pe_string($_POS T['var']);
> $put_info = mysql_query("IN SERT 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_quote s 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.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[frizzle]

Re: 2 small Q's about mysql_real_esca pe_string()


Andy Hassall wrote:[color=blue]
> On 8 Nov 2005 07:15:33 -0800, "frizzle" <phpfrizzle@gma il.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_esca pe_string($_POS T['var']);
> > $put_info = mysql_query("IN SERT 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_quote s 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.u k :: 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_gp c On On
magic_quotes_ru ntime Off Off
magic_quotes_sy base Off Off

Thanks!

Frizzle.