Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Basics: CGI MySQL security

by jfrm (Monk)
on Oct 08, 2006 at 22:58 UTC ( #577045=perlquestion: print w/replies, xml ) Need Help??

jfrm has asked for the wisdom of the Perl Monks concerning the following question:

I have a website that allows viewing and modifying of a MySQL database using PERL CGI scripts and I haven't a clue how to do the security.

People who don't log in should only be able to see limited parts of the data. Others should be able to login and view all parts of the data. Trusted people should be able to login and modify the data.

This is basic stuff and obviously my PERL scripts have to be involved as they must know the identity of the user in order to allow the correct access and show the appropriate forms. But is the identity given to them via .htaccess basic website security, MySQLs own user/password security or perhaps I should ignore both those and write all the user/password stuff within the scripts? Or maybe there is a 4th option I don't know about?

There must be tutorials and guides available on the Internet - but I must be searching using the wrong keywords because I can't find any straightforward instructions giving best practice.

Either advice or a pointer to a website explaining the best approach would be very much appreciated...


Replies are listed 'Best First'.
Re: Basics: CGI MySQL security
by blue_cowdawg (Monsignor) on Oct 08, 2006 at 23:50 UTC
        I have a website that allows viewing and modifying of a MySQL database using PERL CGI scripts and I haven't a clue how to do the security.

    Yikes! That statement scares me! My first reaction to that statement is if you don't have a clue then learn about it before you create the website.

    If I had to solve this problem though, here is one approach I'd consider.

    Consider a "roles based" security system. What I mean by that is this:

    • Anonyomous user. IE a user not logged in.
    • Logged in user
      • Database(s) allowed to access
      • Table(s) allowed to access

    There are many ways to implement a roles based system. The approach that I quite often use is to create a set of tables (outside of what users anonymous and otherwise have access to) such as:

    create table users ( user_id integer not null primary key, login text, passwd text ); create table role_types ( role_type_id integer not null primary key, role_type text ); create table role_map ( role_mapping_id integer not null primary key, role_type_id integer not null references role_types(role_type_id) on delete cascade, user_id integer not null references users(user_id) on delete cascade );

    Using that schema you now have a way to map users to one or more roles that user can have. Keep your business logic of how those roles map to access in your CGI script (for now..)

    That's the first half of it. You now have a table that can be searched to authenticate users, you have a table that defines roles, and you have a mapping table that maps users to their roles. The next step is to track sessions and who owns the session:

    create table sessions ( session_entry_id integer not null primary key, session_id text, session_hack timestamp ); -- Note, session_id is what is used to track the session -- and session_entry_id is used for FK mapping between -- tables create table session_ownership ( session_ownership_id integer not null primary key, session_id integer not null references sessions(session_entry_id) on delete cascade, user_id integer not null references users(user_id) on delete cascade ); create table session_vars ( session_var_id integer not null primary key, var_key text not null, var_value text );

    Note: the primary keys are of type integer, but they could be other types. In most cases I'd use either an auto_increment or a sequence depending on if I were coding this for MySQL or PostgreSQL.

    In the schema I've shown, you have the ability to persist values for sessions between forms within a session instead of using hidden values. Your session_id should be a string of random (unpredictable) characters of sufficient length to make guessing if not impossible at least too difficult to be worth it. I'm mot going into algorithms for that in this post.

    Lastly, in your CGI code you need to provide logic to once in a while (or even every time it is accessed) of visiting the values in the sessions table and deleting rows of sessions that have timed out. Meaning you need to decide how long a session can be idle before you delete the session from the table. In your CGI logic you also need to visit this table between accesses to the CGI to make sure a session is still valid. If it isn't you bounce the user back to the login screen and make them log in again. Optionally you can tell them their session has expired if your nice. :-)

    Now.... mapping your roles to the database(s) and/or table(s) they are allowed to access can either be done with hard coded logic (not the best way IMHO) or via more join tables within your schema. I leave that up to your creativity to figure out from here...

    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

      Don't worry - the data isn't that precious and hardly any of it is there yet, anyway. I'm just doing proof of concepts right now

      Yes, the roles idea has to be used, thanks - also it sounds like you are suggesting that I use the database itself to store users and roles; hadn't thought of that - sounds good.

      I'd like to KISS so would like to avoid doing the authentication myself - having read some more following the ideas in this thread, I think I'm happy with basic http authentication

Re: Basics: CGI MySQL security
by graff (Chancellor) on Oct 09, 2006 at 02:35 UTC
    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.

      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.
Re: Basics: CGI MySQL security
by Hue-Bond (Priest) on Oct 08, 2006 at 23:20 UTC
    Or maybe there is a 4th option I don't know about?

    AFAIK, the standard way to do it is using a session cookie with a value hard to predict. This value is an index into a table that maps to a user of your application. This is way better explained in this merlyn column (offsite) I read a few days ago.

    David Serrano

Re: Basics: CGI MySQL security
by Ryszard (Priest) on Oct 09, 2006 at 11:39 UTC
    I've written quite a few websites with database access and what has been said here is already is pretty decent advice.

    how secure you need to go depends on the level of risk you feel is acceptable to your project.

    use placeholders in your database queries

    when using the DBI the $variable is replaced with a question mark (?). when making the call to the statement you use then $sth->execute(@BindVars). this will be then treated as a litteral, and not interpreted eg select * from dual;drop table users will be inserted into your database, rather than the statement being executed. this is known as an sql injection attack.

    use server side authentication schemes for your users

    any method that uses some javascript (or anyother client side) method is exceptionally vulnerable to attack, and this means your application is compromised.

    instead generate a token server side (md5 of a username/id/secret string) and send it to the browser. at the same time, store it in your mysql database. next time the user requests a page, grab the token from the cookie and compare it to what is in the database.

    as mentioned, make sure your token is non trivally determinable (thats just fancy speak for not easily guessable), and remember, there are a lot smarter people that you and i out there who just love puzzles to break.

    dont be afraid to be paranoid (or, assume the worst)

    make the assumption that everyone out there is out to hack your website. to this end, read about taint checking. think that every input from a user containts carefully crafted cruft to destroy your data.

    check all inputs all the time

    different physical hosts

    as your level of acceptable risk falls (ie, you're less willing to accept risk), think of seperating your presentation and database layers. having your database on a machine that does not contain your webserver, is a good idea. if your webserver is compromised, its going to be just that much harder to compromise your dbserver, especially if you dissallow all access (besides the database of course) from your webserver (eg, via a firewall)

    seperation of power, (or physically paranoid)

    consider seperating your business logic with your presentation layer - physically. create a bunch of libraries that go and talk to your database, and put them on another physical host. have your websever talk to your libraries and have your libraries talk to your database. a classic three tier structure. (hint, there is a reason why it is a classic)

    now your webserver talks to an application server which talks to a database server. a pretty advanced design, and a whole lot of work to implement, especially for a one man band, however, if your webserver is compromised, then there is no direct access to your business layer, which may contain information on how your database works (and hints on how to access it), and how your business processes work, (intentional subtle corruption of data anyone?)

    audit trails/exception handling

    log log log log. log your users access, trap your errors and provide fall thru conditions to conditional logic where appropriate.

    file system security

    make sure all your files have the right permissions, and no more than they should. you could install your webserver into a chrooted gaol/(jail) so if the webserver is hacked, it becomes more difficult to pwn the machine itself.

    Database security

    create your tables with one user role, and have access via another user (without the ability to drop/create objects)

    not a whole lot of practical information per se, but concepts that you should atleast look at and understand while developing your web application.

    some ideas are more extreme than others, and there is a whole bunch of stuff i've left out (firewalls, network seperation, non public routable addressing, proxying etc etc).

    having said that, using placeholders, using taint mode, setting warnings and strict on in your code will help go a long way to providing a secure environment for your application(s)

Re: Basics: CGI MySQL security
by imp (Priest) on Oct 09, 2006 at 02:35 UTC
    Any time you are using data provided by a user in a query you need to be mindful of SQL Injection. Users are bad people and can't ever be trusted.

    You should always use bound parameters when possible, and DBI::quote when it isn't.

Re: Basics: CGI MySQL security
by Khen1950fx (Canon) on Oct 09, 2006 at 02:24 UTC
    As for "best approach", there's a tutorial on using CGI::Application. The author, Jesse Erlbaum, recommends using the Apache::Auth* modules for login security and session management. See the section "Thoughts on Sessions and Security". See: Using CGI::Application
Re: Basics: CGI MySQL security
by jbert (Priest) on Oct 09, 2006 at 10:54 UTC
    A fairly different approach would be to use a web framework. I've looked at Catalyst, and it appears to have a whole lot of modules and plugins which automate the approaches other people have discussed.

    If developing, you'll want both 'Task::Catalyst' and 'Catalyst::Devel' from CPAN.

    Working within the framework is a little different to writing your own code and certainly to start with feels odd. But if you invest the time to learn how not to fight it, you'll get the benefit of pre-written (and debugged!) authentication modules etc.

Re: Basics: CGI MySQL security
by OfficeLinebacker (Chaplain) on Oct 12, 2006 at 14:02 UTC
    Hi. I just wrote a very simple application that uses SQLite. I don't know how much crossover there is, but what I did was create the db as myself, set the perms of the directory it's in to 600, and then added the web server account as rw to the ACLs of the directory and file. The script itself checks the user's identity by using the REMOTE_USER env var. Having a .htacc file in the same directory as the script limits anyone not in a certain security group from even accessing the application. So basically you can't even see the page unless you're logged in and cookied, but that's transparent to me cos that's just how the web server is set up. I also locked down permissions on the directory the script lives in so no one can look at the code. The residual risk is someone who also has privileges to write CGI scripts writes a program to access the database, and in my org. that's apparently OK since there's a vetting process for programmers to get CGI script writing privileges, and it would take a lot of concerted effort and some luck.

    Does that make sense?


    I like computer programming because it's like Legos for the mind.

      This is very helpful thanks. You see I didn't even know about this REMOTE_USER env var but now you've mentioned it, this gave me something to home in on.

      I have now managed to find several pages of the sort that I needed to read, giving me the basics. In particular, for the record, this page is excellent.

      So the answer seems to be that the choices are either to authenticate using basic http authentication or via cookies. Cookies means doing some of the authentication work myself and from my perspective is therefore to be avoided. http has some pitfalls but they can be worked around. Having authenticated, I can use the REMOTE_USER variable in my script and using the ideas of mapping to roles above can then get my scripts to display different things according to the role being used.

      That is good enough for the basic effort I have in mind but of course I will also take note of the advice given above on SQL injection and user paranoia.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://577045]
Approved by GrandFather
Front-paged by bart
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2023-09-29 13:48 GMT
Find Nodes?
    Voting Booth?

    No recent polls found