How can I secure MySQL & CGI?

[Kozz]

I'm reading up on MySQL because I would like to begin implementing it for greater flexibility & speed (as opposed to flat files), especially for databases that could potentially be MUCH larger than just a few hundred records (say, 100,000 or more).

My question is this: What's the best way to store these records if they contain sensitive information such as credit card numbers, names, phone numbers, etc?

The scoop is this: I'm not the only person who has a shell account on this machine which runs both Apache and MySQL (and I do not have root, though I do have some influence with the administrators). In addition, the MySQL DB login information is stored in plain text in the perl script itself right? I don't want somebody else to grab the source of my perl script (either in a shell, or with their own script executing under nobody.nogroup) thereby gaining the MySQL login/pass. Are there some sort of wrappers I should try to use? Anything?

Or if MySQL is a bad idea for this, what should I try to use?

Thanks!

Originally posted as a Categorized Question.

[btrott]

The MySQL docs have some information about keeping passwords secure. I'm not sure they'll help you a huge lot, since you're going to be using some sort of CGI script, yes? Perhaps you could investigate some sort of wrapper, yes--perhaps you could use a wrapper like cgiwrap (isn't that it?) that runs the CGI script as you. Which would mean that you could make the file unreadable by users other than yourself.

Another thing to think about here, just as a reminder--since it sounds like you're dealing with pretty sensitive, critical information--is that MySQL doesn't "do" transactions. Which means that errors can leave your data in a less-than-desirable state. :) (An inconsistent state).

If transactions are important to you, check out PostgreSQL or, if you have a lot of money :), Oracle.

So, you've probably already thought about that, but in case you haven't, I just wanted to warn you. I have no problems w/ MySQL, because I think it's definitely a great product... but just for a particular purpose.

[BBQ]

Extending btrott's well posted advice, I'd really look into Oracle... Apart from supporting transactions, you have very good security methods that you can implement on the database, like roles, table/field encryption, and SSL on the Oracle listeners.

The product itself is not that expensive anymore, considering that you can now purchase a 2 year license based on your CPU's frequency (license method called Universal Power Units).

HTH!

[athomason]

It seems you want to protect three different things: the password stored in your scripts, the password sent over the network while authenticating to mysqld (either on localhost or remotely), and the records themselves. I'll treat each separately.

1) You can secure the passwords stored on disk from everybody but root by putting the login information only in .my.cnf as mentioned in the doc referenced by btrott, and then having all your scripts use that. The line I have in all my scripts is

$dbh = DBI->connect("dbi:mysql:;mysql_read_default_file=/home/username
+/.my.cnf",
                    "", "", {RaiseError => 1 , PrintError => 1});

Of course, make sure .my.cnf has file permissions 0600, or you defeat the whole purpose ;-).

2) MySQL doesn't advertise itself as secure or terribly reliable (as RDBMS's go), but you may be able achieve some security through obscurity from casual packet snoopers, if there is such a thing. According to the O'Reilly DBI book, later versions of MySQL allow you to compress connections. I don't know whether this concerns the only record transmission or the login procedure as well, so I strongly suggest you research it before using it (I couldn't find anything in the mysql doc TOC). In any case, you can use the feature from DBI by passing the attribute "mysql_compression=1" in the connect statement.

3) Apart from the compression, there's not anything you can do with MySQL encryption-wise, AFAIK. Like the other posters mentioned, you really need to look into a more highly powered DB if this concerns you.

[comatose]

You should always use as much security as you can. There's no such thing as too much.

So besides choosing the right database, I recommend some sort of two-way encryption of any sensitive information. There are a variety of methods to accomplish it, and you'll want to choose one that works well in your situation.

One example that I've used takes advantage of a randomly generated password that unlocks each individual order on the site. Without that password, the credit card number is junk. However, we still make it possible to see contact information just in case someone loses the password for that order. Because there's only one person involved in receiving the orders, it's quite practical.

[brd]

Also, when connecting over the network to a MySQL server you should use SSL. Both DBI & MySQL support this. A quick Google search yielded: http://www.star.bnl.gov/STAR/comp/Grid/MySQL/GSI/testing.html

[russmann]

Credit card information should ALWAYS be encrypted if it is stored on disk anywhere, and/or transfered over any wire anywhere. I use PGP/GPG to encrypt CC info written to a MySQL database. The field it writes to is of type TEXT. Encrypting the actual CC data ensure that even if unauthorized people get access to your database, they can't do anything heinous with the data.

[dws]

If you want to secure credit card info, ensure that those bits never resides on a disk visible to the web server, and ensure that database passwords are not directly visible to the web server box. In short, your CGIs will need to communicate with an off-web-server process that has access to the database. (dws)Re: Encrypting Credit card numbers discusses this at greater length.

[cavac]

In modern systems, the handling of payment information (e.g. credit cards) is often implemented on a second server, not on the front-end one. The second server should have a tighter control (e.g. for starters, only very selected users can access it).

The front-end server then talks to the backend to initiate a payment/money transfer, and periodically checks if it succeeded or failed.

As mentioned above, sensitive information should also be encrypted. (In the case of passwords, salted hashes are usually the way to go.)

If your site is a low volume site, you could also hire one the the available online payment services; ask your bank what they suggest. This will take the legal and financial responsibility from you, and you might not have to pay back thousands of dollars if credit card information gets stolen (because you can show that you never asked for that information on your site but deferred the payment process to your bank).

[WebGuy]

Actually, you can perform transactions on mySQL using a more complicated Q-language called T-SQL. I do it at work everyday in other applications. But I would definitely use Oracle if you are storing credit card info!

Just 2 cents from WebGuy!

[Anonymous Monk]

hmm - isn't the CGI-password transmission kinda secure if you channel it thru SSL? and why not use some of the standard encryption modules to en-/decrypt data before/after writing/reading it to/from the DB? (doesn't help much if you can't secure the server, though!)