Requirements: Proposed solution should :

1) Be very lazy (!) And work magically

2) Cover 90-95% percent of all typical SQL queries. We don't want to duplicate SQL functioanality. SQL is an excellent standart. But we found usually uses ≤10% of the SQL abilites / features

3) Be secure enough

4) Work with MySQL. Nor I neither Alex didn't use in our projects other then MySQL database (but this is topic for other post :) )

Research : We test several others solutions (DBIx::*) :

But non of tested soulution doesn't cover our requirmnets because :

1) They are too complicated and so they are not lazy

2) They try to cover all SQL features and gonna to be API between perl code and SQL

3) They require additional efforts to prepare parameters, and don't allow to pass parameters directly from user as-is

So all this modules was development for other tasks and don't fit our ones.

Idea : 1) This is wrappers to DBI functions like Select, Update, Insert, Replace

2) All this functions have similar input parameters : Table_List and reference to hash with parameters

3) SQL query prepared based on this data and executed

Example of this calls are

    @Rec = $dbh->Select('Members', \%Q)
[download]

There %Q - list of parameters usually supplied from user via form or directly. For example

 
   %Q = (login__contain=>'alex')
[download]

4) Security :

4.1) Override significiant fields rule. E.g. run query as

 
    @Rec = $dbh->Select('Members', {%Q, id_m=>1})
[download]

In this case not depending on user conditions only record(s) with id_m=1 will be allowed

4.2) Field name convention rule. All significiant fields should be started with undescore sign. And all fields started from '_' will be removed from user input.

Realization Please check previous post to more example of this idea's realization

Disclaimer: This is still only idea. Altghough this is working idea and working solution. Several projects already done based on this technique and works fine.

Edit ar0n -- added readmore tag