How many times word "login" repeated? I think: too many! How about code like this one: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()"); }
Huh? :-) And now I explain how this work, how keep it secure (this is very important, becouse %Q defined by user in CGI params), simple and usable.my %Q = CGI::Vars(); ... $id_cust=$dbh->Insert("Customer", \%Q) if $Q{pass} eq $Q{confirmpass};
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:
# 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});
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);
To make this possible we define some conventions:
Additional features which we add to these magical functions are:
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 "_":
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 = CGI::Vars(); delete $Q{$_} for grep {m/^_/} keys %Q;
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);
$Q{_balance} = $Q{Set_balance} if $Admin and $Q{Set_balance}; $dbh->Insert("Customer", {%Q, id_cust=>$CustID});
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.
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re: Magical SQL
by lachoy (Parson) on Apr 26, 2002 at 12:36 UTC | |
by ignatz (Vicar) on Apr 26, 2002 at 13:27 UTC | |
by perrin (Chancellor) on Apr 26, 2002 at 15:34 UTC | |
by powerman (Friar) on Apr 26, 2002 at 13:27 UTC | |
by lachoy (Parson) on Apr 26, 2002 at 14:52 UTC | |
by powerman (Friar) on Apr 26, 2002 at 15:23 UTC | |
|
Re: Magical SQL
by Juerd (Abbot) on Apr 26, 2002 at 10:55 UTC | |
by powerman (Friar) on Apr 26, 2002 at 11:24 UTC | |
by Anonymous Monk on Apr 26, 2002 at 11:38 UTC | |
|
Re: Magical SQL
by mpeppler (Vicar) on Apr 26, 2002 at 15:09 UTC | |
|
Re: Magical SQL
by abstracts (Hermit) on Apr 26, 2002 at 10:37 UTC | |
by Sifmole (Chaplain) on Apr 26, 2002 at 12:15 UTC | |
by powerman (Friar) on Apr 26, 2002 at 12:45 UTC | |
|
Re: Magical SQL
by dws (Chancellor) on Apr 26, 2002 at 18:13 UTC | |
by powerman (Friar) on Apr 26, 2002 at 18:51 UTC | |
by autarch (Hermit) on Jun 19, 2002 at 02:38 UTC |