my %Q = CGI::Vars();
...
$id_cust = AddCusomer($Q{login}, $Q{pass}, $Q{name}, $Q{email}, ...)
    if $Q{pass} eq $Q{confirmpass};
...
sub AddCustomer {
    my ($login, $pass, $name, $email, ...) = @_;
    $dbh->do("INSERT INTO Customer SET login=?, pass=?, name=?,
        email=?, ...", undef, $login, $pass, $name, $email, ...)
        or return;
    $dbh->selectrow_array("SELECT LAST_INSERT_ID()");
}
[download]

my %Q = CGI::Vars();
...
$id_cust=$dbh->Insert("Customer", \%Q) if $Q{pass} eq $Q{confirmpass};
[download]

We found simple and clean algorithm how to construct SQL queries from table name and hash with fields. Of course, this is possible now only for simple SQL queries. But our survey results that 98% of SQL queries in typical CGI are simple and can be executed magically with our algorithm.

In short, the algorithm is:

1. Analize database and tables with DESC query. This give us all field names and all primary key names for all tables.
2. Analize given %Q and ignore all keys which not equal to field names in given table "Customer".
3. From other keys construct requested SQL query:
   • Insert() INSERT INTO Table SET "key=value" joined on ","
   • Update() UPDATE Table SET "nonprimarykey=value" joined on "," WHERE "primarykey=value"
   • Replace() REPLACE Table SET "key=value" joined on ","
   • Delete() DELETE FROM Table WHERE "key=value" joined on "AND"
   • ID() SELECT primarykey FROM Table WHERE "key=value" joined on "AND"
   • Count() SELECT count(*) as __count FROM Table WHERE "key=value" joined on "AND"
   • Select() SELECT * FROM Table WHERE "key=value" joined on "AND"
4. Execute query (for Insert() execute additional query: "SELECT LAST_INSERT_ID()").

Example:

# CREATE TABLE Customer (
#       id_cust int auto_increment primary key,
#       login varchar(255) not null,
#       pass varchar(255) not null,
#       name varchar(255) not null,
#       email varchar(255) not null,
#   ) ;

# INSERT INTO Customer SET login='powerman', pass='11111',
#   name='Alex', email='root@localhost' ;
# SELECT LAST_INSERT_ID() ;
%Q = (
    login   => "powerman",
    pass    => "11111",
    name    => "Alex",
    email   => "root@localhost",
    BADKEY  => "QWEQWE",
    );
$id_cust = $dbh->Insert("Customer", \%Q);

# UPDATE Customer SET pass='22222', name='Alex Efros'
#   WHERE id_cust=1 ;
%Q = (
    id_cust => 1,
    pass    => '22222',
    name    => 'Alex Efros',
    );
$dbh->Update("Customer", \%Q);

# REPLACE INTO Customer SET id_cust=1, pass='33333' ;
$dbh->Replace("Customer", {id_cust=>1, pass=>'33333'});

# DELETE FROM Customer WHERE login='powerman' AND pass='33333' ;
$dbh->Delete("Customer", {login=>"powerman", pass=>"33333"});

# SELECT id_cust FROM Customer WHERE login='powerman' AND pass='33333'
+ ;
$id_cust = $dbh->ID("Customer", {login=>'powerman', pass=>'33333'})

# SELECT count(*) as __count FROM Customer
$count = $dbh->Count("Customer");

# SELECT * FROM Customer
@all_customers = $dbh->Select("Customer");

# SELECT * FROM Customer WHERE id_cust=1 ;
$Cust = $dbh->Select("Customer", {id_cust=>1});
[download]

But these queries are not simple, they are too simple. How about more realistic queries? No problem! ;-) At this time our algorithm can produce queries like this one:

# CREATE TABLE Cust (
#       id_cust int auto_increment primary key,
#       id_country int,
#       age int,
#       join_date datetime,
#       _balance decimal(8,2),
#   );
#   CREATE TABLE SelectedGoods (
#       id_sg int auto_increment primary key,
#       id_cust int,
#       id_goods int,
#   );
#   CREATE TABLE Goods (
#       id_goods int auto_increment primary key,
#       goods varchar(255) not null,
#       cost decimal(8,2) not null,
#   );
#   CREATE TABLE Country (
#       id_country int auto_increment primary key,
#       country varchar(255) not null,
#   );

# SELECT
#   Cust.id_cust, Cust.id_country, Cust.age,
#   Cust.join_date, Cust._balance,
#   SelectedGoods.id_sg, SelectedGoods.id_cust,
#   SelectedGoods.id_goods,
#   Goods.goods, Goods.cost,
#   Country.country
# FROM
#   Cust
#   INNER JOIN SelectedGoods ON (Cust.id_cust = SelectedGoods.id_cust)
#   INNER JOIN Goods ON (SelectedGoods.id_goods = Goods.id_goods)
#   INNER JOIN Country ON (Cust.id_country = Country.id_country)
# WHERE
#   Country.country =  'Ukraine'    AND
#   Cust.age        >= 25           AND
#   Cust.join_date  >= DATE_ADD(NOW(), INTERVAL -1 DAY) AND
#   Cust._balance   >  5.50         AND
#   Goods.goods     IN ('Monitor LG','CDROM 52x')
# ORDER BY
#   Cust._balance DESC,
#   Cust.age ASC ;
%Q = (
    country             => 'Ukraine',
    age__ge             => 25,
    join_date__date_ge  => "-1 DAY",
    _balance__gt        => 5.50,
    goods               => ["Monitor LG", "CDROM 52x"],
    -order              => ["_balance DESC", "age"],
    );
@search_result = $dbh->Select(["Customer", "SelectedGoods",
    "Goods", "Country"], \%Q);
[download]

To make this possible we define some conventions:

• Field names start from /^a-z_/ and can't contain "__"
• Tables joined by "INNER JOIN" (see below)
• First field in every table is a primary key and primary key consist of only one field

and you can use these magical functions to access tables which support these conventions, and use standart DBI methods for other tables or for queries too complicated for these magical functions.

Additional features which we add to these magical functions are:

• If table contain field _cdate, this field will be automagically set to NOW() when you Insert() or Replace() in this table.
• If table contain field _mdate, this field will be automagically set to NOW() when you Insert(), Update() or Replace() this table.

Security
Now we have one command: $dbh->Update("Customer", \%Q); and this command can update ANY fields in table "Customer" (remember, all CGI params stored in %Q ?). And fields to update with values set by user running this CGI. Not good, yeah? If customer with id_cust=123 execute our CGI this way:
update_cust.cgi?id_cust=123&_balance=1000000
or this way:
update_cust.cgi?id_cust=1&_balance=0
Yes, right, this customer set own balance to 1000000 and set balance for other customer with id_cust=1 to 0. Terrible!

We solve this problem this way. At first, we do not accept CGI params started from "_":

%Q = CGI::Vars();
delete $Q{$_} for grep {m/^_/} keys %Q;
[download]

And we name all "need to protect" fields in table from "_" (like _balance). But, ... how to set balance when needed? For example, ADMIN _can_ set balance for any USER... No problem. Just do something like this before Update():

$Q{_balance} = $Q{Set_balance} if $Admin and $Q{Set_balance};
$dbh->Insert("Customer", \%Q);
[download]

Ok, but how about accepting id_cust=1 from customer 123 ? Again, no problem. Just do this way ($CustID we have from session):

$Q{_balance} = $Q{Set_balance} if $Admin and $Q{Set_balance};
$dbh->Insert("Customer", {%Q, id_cust=>$CustID});
[download]

We found this sort of "security checks" very good becouse programmer concentrate on important special fields like id_cust and _balance instead of typing "login", "email", and other basic field names many many times.