NAME

DBIx::SecureCGI - Secure conversion of CGI params hash to SQL

SYNOPSIS

 #--- sync

 use DBIx::SecureCGI;

 $row   = $dbh->Select('Table',             \%Q);
 @rows  = $dbh->Select(['Table1','Table2'], {%Q, id_user=>$id});
 $count = $dbh->Count('Table',        {age__gt=>25});
 $id    = $dbh->ID('Table',           {login=>$login, pass=>$pass});
 @id    = $dbh->ID('Table',           {age__gt=>25});
 $newid = $dbh->Insert('Table',       \%Q);
 $rv    = $dbh->InsertIgnore('Table', \%Q);
 $rv    = $dbh->Update('Table',       \%Q);
 $rv    = $dbh->Replace('Table',      \%Q);
 $rv    = $dbh->Delete('Table',       \%Q);
 $rv    = $dbh->Delete(undef,         {id_user=>$id});

 @rows  = $dbh->All('SELECT * FROM Table WHERE id_user=?', $id);
 $row   = $dbh->Row('SELECT * FROM Table WHERE id_user=?', $id);
 @col   = $dbh->Col('SELECT id_user FROM Table');

 $SQL   = $dbh->GetSQL(['Table1','Table2'], \%Q);
 $cache = $dbh->TableInfo(['Table1','Table2']);
 $desc  = $dbh->ColumnInfo('Table');


 #--- async

 use AnyEvent::DBI::MySQL;
 use DBIx::SecureCGI;

 $dbh->Select(…,       sub { my (@rows)        = @_; … });
 $dbh->Count(…,        sub { my ($count)       = @_; … });
 $dbh->ID(…,           sub { my (@id)          = @_; … });
 $dbh->Insert(…,       sub { my ($newid, $dbh) = @_; … });
 $dbh->InsertIgnore(…, sub { my ($rv, $dbh)    = @_; … });
 $dbh->Update(…,       sub { my ($rv, $dbh)    = @_; … });
 $dbh->Replace(…,      sub { my ($rv, $dbh)    = @_; … });
 $dbh->Delete(…,       sub { my ($rv, $dbh)    = @_; … });

 $dbh->All(…, sub { my (@rows) = @_; … });
 $dbh->Row(…, sub { my ($row)  = @_; … });
 $dbh->Col(…, sub { my (@col)  = @_; … });

 $dbh->GetSQL(…,     sub { my ($SQL)   = @_; … });
 $dbh->TableInfo(…,  sub { my ($cache) = @_; … });
 $dbh->ColumnInfo(…, sub { my ($desc)  = @_; … });


 #--- setup

 DBIx::SecureCGI::DefineFunc( $name, '%s op %s' )
 DBIx::SecureCGI::DefineFunc( $name, [ qr/regexp/, '%s op %s' ] )
 DBIx::SecureCGI::DefineFunc( $name, sub { … } )

 $cache = $dbh->SecureCGICache();
 $dbh->SecureCGICache($new_cache);

DESCRIPTION

This module let you use unmodified hash with CGI params to make (or just generate) SQL queries to MySQL database in easy and secure way. To make this magic possible there are some limitations and requirements:

• Your app and db scheme must conform to these "CONVENTIONS"

• Small speed penalty/extra queries to load scheme from db

• No support for advanced SQL, only basic queries

Example: if all CGI params (including unrelated to db table 'Table') are in %Q , then $dbh->Select('Table', \%Q); will execute any simple SELECT query from the table Table (defined by user-supplied parameters in %Q ) and $dbh->Select('Table', {%Q, id_user=>$id}); will make any similar query limited to records with id_user column value $id (thus allowing user to fetch any or his own records).

The module is intended for use only with a fairly simple tables and simple SQL queries. More advanced queries usually can be generated manually with help of $dbh->GetSQL() or you can just use plain DBI methods.

Also is support non-blocking SQL queries using AnyEvent::DBI::MySQL and thus can be effectively used with event-based CGI frameworks like Mojolicious or with event-based FASTCGI servers like FCGI::EV.

Finally, it can be used in non-CGI environment, just to make ease using DBI.

SECURITY OVERVIEW

At a glance, generating SQL queries based on untrusted parameters sent by user to your CGI looks very unsafe. But interface of this module designed to make it safe - while you conform to some "CONVENTIONS" and follow some simple guidelines.

• User have to control over query type (SELECT/INSERT/…).

  It's defined by method name you call.

• User have no control over tables involved in SQL query.

  It's defined by separate (first) parameter in all methods, unrelated to hash with CGI parameters.

• User have no direct control over SQL query.

  All values from hash are either quoted before inserting into SQL, or checked using very strict regular expressions if it's impossible to quote them (like for date/time INTERVAL values).

• You can block/control access to "secure" fields in all tables.

  Name all such fields in some special way (like beginning with _) and when receiving CGI parameters immediately delete all keys in hash which match these fields (i.e. all keys beginning with _). Later you can analyse user's request and manually add to hash keys for these fields before call method to execute SQL query.

• You can limit user's access to some subset of records.

  Just instead of using plain \%Q as parameter for methods use something like { %Q, id_user => $id } - this way user will be limited to records with $id value in id_user column.

Within these security limitations user can do anything - select records with custom WHERE, GROUP BY, ORDER BY, LIMIT; set any values (allowed by table scheme, of course) for any fields on INSERT or UPDATE; etc. without any single line of your code - exclusively by using different CGI parameters.

HOW IT WORKS

Each CGI parameter belongs to one of three categories:

• related to some table's field in db

  fieldname AND fieldname__funcname

• control command for DBIx::SecureCGI

  __command

• your app's parameter

It's recommended to name fields in db beginning with lowercase letter or underscore, and name your app's parameters beginning with Uppercase letter to avoid occasional clash with field name.

To protect some fields (like balance or privileges) from uncontrolled access you can use simple convention: name these fields in db beginning with _; when receiving CGI params just delete all with names beginning with _ - thus it won't be possible to access these fields from CGI params. DBIx::SecureCGI doesn't know about this and handle these fields as usual fields, so you should later add needed keys for these fields into hash you'll give to DBIx::SecureCGI methods. This way all operations on these fields will be controlled by your app.

You can use any other similar naming scheme which won't conflict with "CONVENTIONS" below - DBIx::SecureCGI will analyse db scheme (and cache it for speed) to detect which keys match field names.

CGI params may have several values. In hash, keys for such params must have ARRAYREF value. DBIx::SecureCGI support this only for keys which contain __. Depending on used CGI framework you may need to convert existing CGI parameters in this format.

Error handling: all unknown keys will be silently ignored, all other errors (unable to detect key for joining table, field without __funcname have ARRAYREF value, unknown __funcname function, etc.) will return usual DBI errors (or throw exceptions when {RaiseError=>1}.

CONVENTIONS

• Each table's first field must be a PRIMARY KEY.

  MOTIVATION: DBIx::SecureCGI uses simplified analyse of db scheme and suppose first field in every table is a PRIMARY KEY. To add support for complex primary keys or tables without primary keys we should first define how $dbh->ID() should handle them and how to automatically join such tables.

• Two tables are always JOINed using field which must be PRIMARY KEY at least in one of them and have same name in both tables.

  So, don't name your primary key id if you plan to join this table with another - name it like id_thistable or thistableId.

  If both tables have field corresponding to PRIMARY KEY in other table, then key field of right table (in order defined when you make array of tables in first param of method) will be used.

  If more than two tables JOINed, then each table starting from second one will try to join to each of the previous tables (starting at first table) until it find table with suitable field. If it wasn't found DBI error will be returned.

  MOTIVATION: Let DBIx::SecureCGI automatically join tables.

• Field names must not contain __ (two adjoined underscore).

  MOTIVATION: Distinguish special params for DBIx::SecureCGI from field names. Also, DBIx::SecureCGI sometimes create aliases for fields and their names begins with __.

• Hash with CGI params may contain several values (as ARRAYREF) only for key names containing __ (keys unrelated to fields may have any values).

  MOTIVATION: Allowing { field => \@values } introduce many ambiguities and in fact same as { field__eq => \@values }, so it's safer to deny it.

Hash to SQL convertion rules

• __something

  Keys beginning with __ are control keys. Supported keys are:

  __order

  Define value for ORDER BY. Valid values are "field_name" or "field_name ASC" or "field_name DESC". Multiple values can be given as ARRAYREF.

  __group

  Define value for GROUP BY. Valid values are same as for __order.

  __limit

  Can have up to two numeric values (when it's ARRAYREF), set LIMIT.

  __force

  If the value of __force key is true, then it's allowed to run $dbh-Update() > and $dbh-Delete() > with an empty WHERE. (This isn't a security feature, it's just for convenience to protect against occasional damage on database while playing with CGI parameters.)

• fieldname__funcname

  If the key contains a __ then it is treated as a field__function. If the there is no field with this name in database, this key is ignored. A valid key value - scalar or a reference to an array of scalars. A list of available functions in this version is shown below.

  Unless special behavior mentioned functions handle ARRAYREF value by applying itself to each value in array and joining with AND.

  Example: { html__like => ["%<P>%", "%<BR>%"] } will be transformed to "html LIKE '%<P>%' AND html LIKE '%<BR>%'".

  Typically, such keys are used in WHERE, except when funcname begins with set_ - such keys will be used in SET.

• fieldname

  Other keys are treated as names of fields in database. If there is no field with such name, then key is ignored. A valid value for these keys - scalar.

  Example: { name => "Alex" } will be transformed to "name = 'Alex'" in SQL.

  Typically, such keys are used in part SET, except for PRIMARY KEY field in $dbh->Update() - it will be placed in WHERE.

INTERFACE

DefineFunc( $name, '%s op %s' )

DefineFunc( $name, [ qr/regexp/, '%s op %s' ] )

DefineFunc( $name, sub { … } )

Define new or replace existing function applied to fields after __ delimiter.

SQL expression for that function will be generated in different ways, depending on how you defined that function - using string, regexp+string or code:

    $expr = sprintf '%s op %s', $field, $dbh->quote($value);
    $expr = $value =~ /regexp/ && sprintf '%s op %s', $field, $value;
    $expr = $code->($dbh, $field, $value);

If $expr will be false DBI error will be returned. Here is example of code implementation:

    sub {
        my ($dbh, $f, $v) = @_;
        if (… value ok …) {
            return sprintf '…', $f, $dbh->quote($v);
        }
        return;     # wrong value
    }

INTERFACE injected into DBI

GetSQL( $table, \%Q )

GetSQL( $table, \%Q, sub { my ($SQL) = @_; … })

GetSQL( \@tables, \%Q )

GetSQL( \@tables, \%Q, sub { my ($SQL) = @_; … })

This is helper function which will analyse (cached) database scheme for given tables and generate elements of SQL query for given keys in %Q. You may use it to write own methods like $dbh->Select() or $dbh->Insert().

In %Q keys which doesn't match field names in $table / @tables are ignored.

Names of tables and fields in all keys (except {Table} and {ID}) are escaped, field names qualified with table name (so they're ready for inserting into SQL query). Values of {Table} and {ID} should be escaped with $dbh->quote_identifier() before using in SQL query.

Returns HASHREF with keys:

{Table}

first of the used tables

{ID}

name of PRIMARY KEY field in {Table}

{Select}

list of all field names which should be returned by SELECT * excluding duplicated fields: if SELECT used on more than one table with same field "fieldname" then {Select} will include only name from first table "tablename.fieldname"; field names in {Select} are joined with ","

{From}

all tables joined using chosen JOIN type (INNER by default)

{Set}

string like "field=value, field2=value2" for all simple "field" keys in %Q

{Where}

a-la {Set}, except fields joined using AND and added "field__function" fields; if there are no fields it will be set to string "1"

{UpdateWhere}

a-la {Where}, except it uses only "field__function" keys plus one PRIMARY KEY "field" key (if it exists in %Q )

{Order}

string like "field1 ASC, field2 DESC" or empty string

{Group}

a-la {Order}

{Limit}

{SelectLimit}

Both set to values of __limit if it contain one number; if __limit contain two numbers, then {Limit} will be empty, and {SelectLimit} will contain both numbers joined with ","

Insert( $table, \%Q )

Insert( $table, \%Q, sub { my ($newid, $dbh) = @_; … }) )

    INSERT INTO {Table} SET {Set}

Return $dbh->{mysql_insertid} on success or undef() on error.

It's strongly recommended to always use {%Q, …, primary_key=>undef}, because if you didn't force primary_key field to be NULL (and thus use AUTO_INCREMENT) then user may send CGI parameter to set it to -1 or 4294967295 and this will result in DoS because no more records can be added using AUTO_INCREMENT into this table.

InsertIgnore( $table, \%Q )

InsertIgnore( $table, \%Q, sub { my ($rv, $dbh) = @_; … })

    INSERT IGNORE INTO {Table} SET {Set}

Return $rv (true on success or undef() on error).

Update( $table, \%Q )

Update( $table, \%Q, sub { my ($rv, $dbh) = @_; … })

    UPDATE {Table} SET {Set} WHERE {UpdateWhere} [LIMIT {Limit}]

Uses in SET part all fields given as field, in WHERE part all fields given as field__function plus PRIMARY KEY field if it was given as field.

Return $rv (amount of modified records on success or undef() on error).

To use with empty WHERE part require {__force=>1} in %Q.

Replace( $table, \%Q )

Replace( $table, \%Q, sub { my ($rv, $dbh) = @_; … })

    REPLACE INTO {Table} SET {Set}

Uses in SET part all fields given as field.

Return $rv (true on success or undef() on error).

Delete( $table, \%Q )

Delete( $table, \%Q, sub { my ($rv, $dbh) = @_; … })

Delete( \@tables, \%Q )

Delete( \@tables, \%Q, sub { my ($rv, $dbh) = @_; … })

Delete( undef, \%Q )

Delete( undef, \%Q, sub { my ($rv, $dbh) = @_; … })

    DELETE FROM {Table} WHERE {Where} [LIMIT {Limit}]

Delete records from $table or (one-by-one) from each table in @tables. If undef() given, then delete records from ALL tables (except TEMPORARY) which have ALL fields mentioned in %Q.

To use with empty WHERE part require {__force=>1} in %Q.

Return $rv (amount of deleted records or undef() on error). If used to delete records from more than one table - return $rv for last table. If error happens it will be immediately returned, so some tables may not be processed in this case.

ID( $table, \%Q )

ID( $table, \%Q, sub { my (@id) = @_; … })

ID( \@tables, \%Q )

ID( \@tables, \%Q, sub { my (@id) = @_; … })

    SELECT {ID} FROM {From} WHERE {Where}
    [ORDER BY {Order}] [LIMIT {SelectLimit}]

Execute SQL query using $dbh->Col().

Count( $table, \%Q )

Count( $table, \%Q, sub { my ($count) = @_; … })

Count( \@tables, \%Q )

Count( \@tables, \%Q, sub { my ($count) = @_; … })

    SELECT count(*) __count FROM {From} WHERE {Where}

Execute SQL query using $dbh->Col().

Select( $table, \%Q )

Select( $table, \%Q, sub { my (@rows) = @_; … })

Select( \@tables, \%Q )

Select( \@tables, \%Q, sub { my (@rows) = @_; … })

    SELECT * FROM {From} WHERE {Where}
    [ORDER BY {Order}] [LIMIT {SelectLimit}]

    SELECT *, count(*) __count FROM {From} WHERE {Where} GROUP BY {Group}
    [ORDER BY {Order}] [LIMIT {SelectLimit}]

Instead of SELECT * it uses enumeration of all fields qualified using table name; if same field found in several tables it's included only one - from first table having that field.

In @tables you can append " LEFT" or " INNER" to table name to choose JOIN variant (by default INNER JOIN will be used):

    $dbh->Select(['TableA', 'TableB LEFT', 'TableC'], …)

Execute request using $dbh->All() when called in list context or using $dbh->Row() when called in scalar context.

All( $sql, @bind )

All( $sql, @bind, sub { my (@rows) = @_; … })

Shortcut for this ugly but very useful snippet:

    @{ $dbh->selectall_arrayref($sql, {Slice=>{}}, @bind) }

Row( $sql, @bind )

Row( $sql, @bind, sub { my ($row) = @_; … })

Shortcut for:

    $dbh->selectrow_hashref($sql, undef, @bind)

If you wonder why it exists, the answer is simple: it was added circa 2002, when there was no $dbh->selectrow_hashref() yet and now it continue to exists for compatibility and to complement $dbh->All() and $dbh->Col().

Col( $sql, @bind )

Col( $sql, @bind, sub { my (@col) = @_; … })

Shortcut for:

    $scalar = $dbh->selectcol_arrayref($sql, undef, @bind)->[0]
    @array  = @{ $dbh->selectcol_arrayref($sql, undef, @bind) }

SecureCGICache()

SecureCGICache( $new_cache )

Fetch (or set when $new_cache given) HASHREF with cached results of DESC tablename SQL queries for all tables used previous in any methods.

You may need to reset cache (by using {} as $new_cache value) if you've changed scheme for tables already accessed by any method or if you changed current database.

Also in some environments when many different $dbh used simultaneously, connected to same database (like in event-based environments) it may make sense to share same cache for all $dbh.

TableInfo( $table, sub { my ($cache) = @_; … })

TableInfo( \@tables, sub { my ($cache) = @_; … })

Ensure DESC tablename for all $table / @tables is cached.

Return $dbh->SecureCGICache() on success or undef() on error.

ColumnInfo( $table, sub { my ($desc) = @_; … })

Ensure DESC $table is cached.

Return $dbh->All("DESC $table") on success or undef() on error.

__FUNCTIONS for fields

These functions can be added and replaced using DBIx::SecureCGI::DefineFunc().

Functions which can be used in %Q as fieldname_funcname:

eq, ne, lt, gt, le, ge

 field =  value     field IS NULL
 field != value     field IS NOT NULL
 field <  value
 field >  value
 field <= value
 field >= value

For functions eq or ne:

 eq []            - NOT 1
 ne []            - NOT 0
 eq only    undef - name IS NULL
 ne only    undef - name IS NOT NULL
 eq without undef - name IN (...)
 ne without undef - (name IS NULL OR name NOT IN (...))
 eq with    undef - (name IS NULL OR name IN (...))
 ne with    undef - name NOT IN (...)

where

 "[]"           : name__func=>[]
 "only    undef": name__func=>undef    or name__func=>[undef]
 "without undef": name__func=>$defined or name__func=>[@defined]
 "with    undef": name__func=>[@defined_and_not_defined]

like, not_like

 field LIKE value
 field NOT LIKE value

date_eq, date_ne, date_lt, date_gt, date_le, date_ge

 field =  DATE_ADD(NOW(), INTERVAL value)
 field != DATE_ADD(NOW(), INTERVAL value)
 field <  DATE_ADD(NOW(), INTERVAL value)
 field >  DATE_ADD(NOW(), INTERVAL value)
 field <= DATE_ADD(NOW(), INTERVAL value)
 field >= DATE_ADD(NOW(), INTERVAL value)

value must match /^-?\d+ (?:SECOND|MINUTE|HOUR|DAY|MONTH|YEAR)$/

set_add

 field = field + value

When used in $dbh->Update() it will be in SET instead of WHERE. It doesn't make sense to use this function with $dbh->Insert(), $dbh->InsertIgnore() or $dbh->Replace().

set_date

 field = NOW()
 field = DATE_ADD(NOW(), INTERVAL value)

If it's value is (case-insensitive) string "NOW" then it'll use NOW() else it will use DATE_ADD(…).

When used in $dbh->Insert(), $dbh->InsertIgnore(), $dbh->Update() and $dbh->Replace() it will be in SET.

BUGS AND LIMITATIONS

No bugs have been reported.

Only MySQL supported.

It's impossible to change PRIMARY KEY using $dbh->Update() with:

 { id => $new_id, id__eq => $old_id }

because both id and id__eq will be in WHERE part:

 SET id = $new_id WHERE id = $new_id AND id = $old_id

and if we won't add 'id => $new_id' in WHERE part if we have 'id__eq' , then we'll have do use this '($table, {%Q, id_user=>$S{id_user}, id_user__eq=>$S{id_user})' in all CGI requests to protect against attempt to read someone else's records or change own records's id_user field by using 'id_user' or 'id_user__eq' CGI params.

SUPPORT

Please report any bugs or feature requests through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-SecureCGI. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

You can also look for information at:

• RT: CPAN's request tracker

  http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-SecureCGI

• AnnoCPAN: Annotated CPAN documentation

  http://annocpan.org/dist/DBIx-SecureCGI

• CPAN Ratings

  http://cpanratings.perl.org/d/DBIx-SecureCGI

• Search CPAN

  http://search.cpan.org/dist/DBIx-SecureCGI/

AUTHORS

Alex Efros <powerman@cpan.org>

Nikita Savin <nikita@asdfGroup.com>

LICENSE AND COPYRIGHT

Copyright 2002-2013 Alex Efros <powerman@cpan.org>.

This program is distributed under the MIT (X11) License: http://www.opensource.org/licenses/mit-license.php

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

cpanm DBIx::SecureCGI

perl -MCPAN -e shell
install DBIx::SecureCGI