Safe insert queries for mysql ?

[frizzle]

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_string($_POST['comment'). now i've seen
mysql_real_escape_string, and i was wondering if there's a
big difference between them, but most of all, i was wondering
if 'addslashes()' is safe enough, because i noticed that
stripslashes() doesn't strip all 'mysql_escape_string' slashes,
but does strip all 'addslashes()' ... :-s

I know there's something called mySQL-injection, and if i
got it correctly, that would mean executing queries
e.g. by submitting a " and then a query ...
of course i want to prevent this.

I hope this kinda makes sense ... :-)

Greetings Frizzle.

[Malcolm Dew-Jones]

frizzle (ph********@gmail.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_string($_POST['comment'). now i've seen
: mysql_real_escape_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_string is deprecated, and replaced with
mysql_real_escape_string, which is basically "identical" in functionality.

mysql_real_escape_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_string with
mysql_real_escape_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_string' 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]

On 6 Sep 2005 14:27:47 -0700, "frizzle" <ph********@gmail.com> wrote:

I was wondering the folllowing: when i insert something
into a mySQL DB -in a guestbook for instance- i mostly use
mysql_escape_string($_POST['comment').

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_escape_string($value_x) . "'," .
"'" . mysql_real_escape_string($value_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 :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[frizzle]

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.