[thelist] PHP dynamic INSERT's

shawn allen shawn at alterior.net
Tue Feb 4 13:16:12 CST 2003


quoth Dan Horning:
> > When inserting records into a database from PHP and an HTML form,
> > ensure the names of the form fields correspond to the names of
> > columns in your table, then you can use this code to quicly build an
> > INSERT string:
> >
> > 	$insert = "INSERT INTO ".$tablename."(";
> > 	$insert .= implode(",",$columns).") VALUES
> >                (\"".implode("\",\"",$values)."\")";
>
> I'd be cautious of this tip b/c you open some vulnerabilities into
> your database structure..

That's true with any statement built on user input, but it's easy to
prevent: just validate the input properly. Verify the table exists, and
that the values in $columns are actually exist in the table. Properly
quote (double quotes won't work in other RDBMS's, btw) and escape your
values, and this should work just fine.

That said, here's a bit of an improvement on Rob's original.

    <?php
    function db_quote(&$value, $key, $table)
    {
        $value = sprintf("'%s'", mysql_escape_string($value));
    }

    /*
     * $row should be an associative array in which keys are the column
     * names.
     */
    function db_insert_statement($row, $table)
    {
        array_walk($row, 'db_quote', $table);

        return sprintf('INSERT INTO %s (%s) VALUES (%s)',
                       $table,
                       join(', ', array_keys($row)),
                       join(', ', array_values($row)));
    }

    // test
    $row = array('foo' => 'bar', 'baz' => "qux's");
    print db_insert_statement($row, 'quux');
    ?>

This should output:

    INSERT INTO quux (foo, baz) VALUES ('bar', 'qux\'s');

If you're not using MySQL, you'll probably want to provide another
escaping function for use in db_quote() (such as addslashes()). If
you're going to do write your own, be sure to take magic quotes into
account, as request vars (as well as values pulled from a DB) may be
"escaped" already.

Using array_walk() will allow you to make exceptions for specific
columns and tables -- I use it in a CMS to set up "handlers" for columns
with relationships, and values requiring pre-INSERT transformations
(some of the data is submitted as nested structures, which I then have
to do some work on to get the actual "value"). An equivalent function
that creates UPDATE statements would be very similar: just change the
statement syntax a bit, and add a WHERE clause. :)

HTH,

--
shawn allen
  mailto://shawn@alterior.net
  phone://415.577.3961
  http://alterior.net
  aim://shawnpallen




More information about the thelist mailing list