Safe insert queries for mysql ?

**Malcolm Dew-Jones** · Sep 6 '05, 10:15 PM

Re: Safe insert queries for mysql ?

frizzle (phpfrizzle@gma il.com) wrote:
: Hi there,

: I was wondering the folllowing: when i insert something
: into a mySQL DB -in a guestbook for instance- i mostly use
: mysql_escape_st ring($_POST['comment'). now i've seen
: mysql_real_esca pe_string, and i was wondering if there's a
: big difference between them, but most of all, i was wondering

The manual says that mysql_escape_st ring is deprecated, and replaced with
mysql_real_esca pe_string, which is basically "identical" in functionality.

mysql_real_esca pe_string is better because it considers the character set
of the database (connection?) to ensure that all the correct things are
escaped, where as the old function does not do that.

In other words you should replace mysql_escape_st ring with
mysql_real_esca pe_string, if you have the necessary version of php, and
everything should continue to work as before (though the new function
needs a database connection, so it is not quite a drop in replacement).

: if 'addslashes()' is safe enough, because i noticed that
: stripslashes() doesn't strip all 'mysql_escape_s tring' slashes,
: but does strip all 'addslashes()' ... :-s

You should use the escape routine that is specific to what you are doing
to be sure the correct things are escaped. I.e. If you are sending data
to a database you should escape using the database escape routine. If you
were sending literal data to html then you would escape using an html
escape routine. If you are using literal data in regular expressions then
use that escape routine (quotemeta() I believe), etc, etc...

(If you have the magic quote stuff turned on then you may end up escaping
some things twice, which is a bug you would want to fix, but that will not
normally be a security risk.)

--

This programmer available for rent.

**Andy Hassall** · Sep 6 '05, 10:35 PM

Re: Safe insert queries for mysql ?

On 6 Sep 2005 14:27:47 -0700, "frizzle" <phpfrizzle@gma il.com> wrote:
[color=blue]
>I was wondering the folllowing: when i insert something
>into a mySQL DB -in a guestbook for instance- i mostly use
>mysql_escape_s tring($_POST['comment').[/color]

Right, there's the problem to start with: "mostly use". At some point you will
forget to use it, and you've got a potential SQL injection attack opened up.

I recommend using the ADOdb library (http://adodb.sourceforge.net/). This puts
a wrapper around database access which handles escaping for you by emulating
placeholders - or using the database's native support if it has it (older MySQL
doesn't, newer MySQL does, as do other databases such as Oracle that it
supports).

Instead of using the raw MySQL calls and embedding values into SQL:

$res = mysql_query(
"insert into t (x, y) values (" .
"'" . mysql_real_esca pe_string($valu e_x) . "'," .
"'" . mysql_real_esca pe_string($valu e_y) . "'" .
")"
);

... you'd use the ADOdb functions such as:

$db->Execute(
'insert into t (x,y) values (?,?)',
array($value_x, $value_y)
);

The library does whatever is required to get the values into the database -
consistently and without you having to worry about quotes, escaping and so on.

If you follow the simple rule that you never put variables (or at least, user
input) into SQL statement strings, you always "bind" them separately like this,
it eliminates the vast majority of SQL injection issues.

--
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** · Sep 6 '05, 10:35 PM

Re: Safe insert queries for mysql ?

Thanks for the answer!
I totally understand the first part!
Unfortunately, i don't quite understand the
last part about addslashes etc. Probably my
lack of knowledge of the English language, but
could you somehow try to explain it in an different
way?

thanks anyway!

Greetings Frizzle.

Safe insert queries for mysql ?

Safe insert queries for mysql ?

Comment

Comment

Comment