Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re: Basics: CGI MySQL security

by graff (Chancellor)
on Oct 09, 2006 at 02:35 UTC ( #577062=note: print w/replies, xml ) Need Help??

in reply to Basics: CGI MySQL security

I'm not quite an expert on this yet myself, but I do need to improve in this area, so I'll make some suggestions and see if other monks shoot me offer improvements... 8^)

On the one hand, there are mysql user accounts that can be set up with a varied assortment of permissions for what they may do with specific databases, specific tables and even specific columns within tables. I would tend to use the mysql user accounts to establish the "roles" that blue_cowdawg spoke of:

  • a role for "anonymous" users only has query permissions on a limited set of tables and columns
  • a role for "known/logged-in but not expert" users provides a wider range of query permissions, and possible has permission to insert rows here or there when necessary (e.g. to keep a log of query activity for future (re)use)
  • the "expert" role can query, insert, update and even delete things (but be careful about granting delete permission).

On the other hand, you have the web login procedure, with user names and passwords assigned to any number of people out in the world. The relationship between these user accounts and the mysql "role" user accounts is something that needs to be managed: someone has to be responsible for approving new people (new web login accounts) who want to take on any of the more privileged (mysql account) roles.

With that set up, the cgi scripts simply need to maintain and use the session data to know, from one page/transaction to the next, what sort of client they are dealing with on each request.

Suppose that there's a cgi script that provides a form for updating table data (something only an "expert" can do). If an unauthorized user happens to get hold of an appropriate URL string to send a request to that script, the script will check for suitable "expert" credentials in the session data for the request: if the user is known/logged-in, query the sort of table that blue_cowdawg described, relating login names to database roles. If the appropriate role assignment isn't there, or if the user is not logged in, the cgi script will return a "permission denied" response.

As for the query activity itself, you must of course use the necessary precautions equally for all users when putting together the SQL statements: do taint checking on all user input, use placeholders for query parameter values, and don't use user-supplied strings as a direct part of any SQL string that you pass to the database. That is, instead of something like this, which would be outrageously risky:

$table = param("table"); $column = param("column"); $value = param("value"); $rows = $dbh->selectall_arrayref( "select $column from $table where ke +y = '$value'" );
you should be doing something like this, to eliminate the risk:
my %table_cols = ( foo_table => 'foo_column', bar_table => 'bar_column', # ... ); my $table = param( "table" ); my $column; if ( defined( $table ) and $table =~ /^(\w+)$/ and exists( $table_cols{$1} ) my $sth = $dbh->prepare( "select $table_cols{$1} from $1 where key += ?" ); $sth->execute( $param('value')); my $rows = $sth->fetchall_arrayref; }
Except that even this last snippet does not provide the amount of error checking that would be prudent: for each DBI call that could fail, you wan the cgi script to check for failure, and in that case, do something coherent in terms of returning some sort of feedback to the client.

Replies are listed 'Best First'.
Re^2: Basics: CGI MySQL security
by jfrm (Monk) on Oct 12, 2006 at 21:18 UTC

    This seems great for super-secure access. But really, I'm trying to keep it simple and a minimal level of security will suffice. If I have some basic login using cookies or http, is it necessary for me also to have the additional security using MySQL logins? I was hoping that the basic web security would be enough and my scripts would all just use the same MySQL ID to access the database...

      No, it's not really necessary to have separate MySQL accounts; if your cgi scripting is reasonably well designed and safe, and you're not dealing with content that poses an attractive target for attack or abuse, a single MySQL account is fine.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://577062]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (3)
As of 2022-06-27 05:42 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (86 votes). Check out past polls.