Security: SQL Insertion

[VBCoder]

Does the DB query_insert handle escaping of nasty characters (parenthesis, commas, quotes, etc.) - to avoid SQL insertion attacks, or just plain SQL corruption?

(I know the Perl DBI does all of this for you, saving Perl programmers from many of the headaches - and breakins - common to PHP)

Also, why does the vB code *never* use the query_insert method?

[Andreas]

It doesn't, it's just a shortcut.

But AFAIK the new DB Class supports prepared statements, if that is what you want.

Edit: mysqli supports it, the new DB class doesn't.

[VBCoder]

Yeah, mysqli is great. Once you've used the power of DBI over roll-your-own-string-SQL, you can't go back.

Kirby, then, can I ask you: What is the best way, in vB / PHP, to ensure that all data passed to the INSERT is quoted and escaped properly?

(Doing it by hand can get tricky, with nulls, quotes, slashes, charsets, etc. - especially when you don't want to strip anything, just get it to the DB safely).

[sabret00the]

addslashes();

but make sure you use it on a new variable, not $_POST/$_GET/$_REQUEST/$_SERVER/$_COOKIE/etc

[Andreas]

$db->escape_string()

[The Geek]

$db->escape_string($string) is the best way AFAIK.

[VBCoder]

Yeah, addslashes is not sufficient, for a lot of reasons.

I took a look at escape_string - it defaults to mysql_escape_string(), which is good, except:

This function became deprecated, do not use this function. Instead, use mysql_real_escape_string().

But at least we're somewhere...

It's a shame that PHP programmers have to go through such hoolahoops for what should be basic DB class functionality (again, see DBI for an example)... Building your own string is not only ineffecient, (and a hassle for the programmer) - it opens the door to a lot of security issues and internationalization bugs

[Andreas]

I took a look at escape_string - it defaults to mysql_escape_string()


Not really ;)


if (function_exists($this->functions['real_escape_string']))
{
$this->functions['escape_string'] = $this->functions['real_escape_string'];
}



I guess vB does not use prepared statements due to compatibilty reasons.

[VBCoder]

Kirbs, you are the master!