John W. Holmes wrote:
I wrote a db class which builds my queries for me. Example snippit:

/* -- db.conf -- (table definitions) */
dbNewConn('conn', 'user:password@host');
dbNewDb('database', 'conn');
dbNewTable('my_table', 'database.table_name');

/* -- My script.php -- */
$d = array( 'name' => $_POST['name'],
            'email' => $_POST['email']);

dbUpdate('my_table', $d, "id='{$_POST['id']}'");

How do you differentiate between string updates and integer/float
updates? Or do you just put quotes around the numbers, too?
On the 'input' side I use is_numeric() to test if the data is numeric. But I haven't considered testing the field type itself. It's easy enough to add. This hasn't come up yet in any of our development.

As dbUpdate gets executed, if the connection isn't up, it connects to
the db server, next if it doesn't have a list of fields for the table

lists the fields and 'caches' them during the script execution. And
finally it builds the query string. The resulting query is:

UPDATE database.table_name SET `name`='The Name',
`email`='[EMAIL PROTECTED]' WHERE id=1;

Wouldn't the 1 have quotes around it?
Yes it would, that was my typo... it should be id='1' but id I'm going to assume is an int auto_increment, so the correct where clause should be "id={$_POST['id']}"

It handles all escaping, mysql functions etc. (so I could do: 'name'

'PASSWORD('.$_POST['name'].')' and it would be escaped propperly).

code is running in a production application and have no had any
performance problems.

How would you escape that? The contents of the PASSWORD function needs
to be surrounded by quotes (unless you're passing a column).
There's a "private" function which escapes all data and tests for the function etc.

In the example data below: "PASSOWRD('O'mallery')" it would do:

1) Determine if it is a function:
Substr up to first '(' and see if the strtoupper of it is in_array() which has all of the mysql functions.
2) Since it know's it's a function, it then takes the next char after the '(' and if it's either a ' or " it does the next step.
3) Takes first part "PASSWORD(" and then the data portion "O'mallery" and replaces quote with \quote in data. (This takes into account if you passed the string 'PASSWORD("O'Mallery")' it would not escape the '.) Next it rebuilds the string "PASSWORD(<quote>$DATA<quote>)"

Here is the code:
function dbPrepValue($value)
global $_DB_;

if ( is_numeric($value) )
return $value;
$tmp = strtoupper($value);
if ( 0 < ($t = strpos($tmp, '(') ) )
$tmp = substr($tmp, 0, ($t - 1) );

if ( in_array( $tmp, $_DB_['mysqlFunc'] ) )
$q = substr($tmp, ($t + 1), 1);
if ( $q == "'" || $q == '"' )
$func = substr($value, 0, $t);
$data = substr($value, ($t + 1), -2);
$value = "{$func}{$q}" . str_replace($q, "\{$q}", $data) . "{$q})";

return $value;
return "'{$value}'";

While this code does not take into account EVERY valid query statement, for those conditions you can just pass a raw query to dbQuery(). I'll post a link to the class sometime this week.


So you'd need

'name' => "PASSWORD('" . $_POST['name'] . "')"

wouldn't you? So if the name is "O'mallery" you'd end up with

'name' => "PASSWORD('O'mallery')"

How do you escape that without also escaping the quotes that delimit the

---John Holmes...

PHP General Mailing List (
To unsubscribe, visit:

Reply via email to