MySQL security issues

[scottward]

I am experienced in database development, but am just getting started with the MySQL/PHP combo. Any assistance with the following issue will be greatly appreciated.

I am designing an database for a client that will contain product and order information among other things. The database will store credit card information. Obviously, there will need to be a username/password set up for people who browse the web site that allows them to only SELECT (to view products) and INSERT (when they place an order). However, I am a little concerned about the possibility of someone finding the embedded password/username and being able to view the credit card info.

I have been reading about MySQL Grant Tables. It seems that the ideal solution would be to deny the generic user SELECT rights on the table that holds the credit card info. However, since I don't have root access, I can't manipulate the Grant Tables (right?).

So my questions are:
1) Is there a way for me to manipulate the Grant Tables?

2) What are some quick tips on how to make sure that users don't enter something in a field that would end the SQL statement and start another that would drop the database or print out the usernames and passwords?

3) The main question is: given the situation (I want to store credit card info in a MySQL database), how can I make sure that no one except authorized users view the information? What can I do to insure that I have done "due diligence" as far as protecting my client's customer's order information? Do I even need to mess with the Grant Tables?

Thanks so much for your help.

Sincerely,

Scott Ward

[TakeIT2]

This is a good general start for a how to...

ok, should we also assume that the form pages are running on secure server?

I looked and ops.powweb.com does not allow "all" Grants, and you cant touch the security of the server(where Grants resides) from outside root/admin-rights. On Privileges of users you could have the part of the script that writes to the tables, the card info only have the right to write and not read. it would be a one way opperation, and if you added an encryption layer via a secondary language from within the php so it becomes less preditable how it is handled from the "outside security."

I have never done this before either. I like the way you are looking at the common aspects of scripting this and the problematics involved.

I don't know, I hope somebody comments that knows more...
(probably won't be to hard)

Paul

[scottward]

Thanks for your reply. Here are some responses:

1) Yes, the PHP scripts used to write/read the credit card info would run on a secure server.

2) You're right, I could have the script that writes to the credit card table connect to the database as a user who only has write capability. However, there is going to have to be a public user that has SELECT rights in order to display the products in the database. And again, since we can't edit the grant tables, we can't let the public user have SELECT rights on the products table but restrict that right on the credit card table. Its the whole database or nothing (I think).

3) I thought of encrypting the credit card info. It seems like you should be able to securely store the data using the mechanisms inherent in MySQL. However, I am open to the encryption idea. How would I go about incorporating encryption using Powweb's installation of PHP?

Scott

[TakeIT2]

Selecting product, is not the security issue I would not think, unless you make it all a part of the same statement. if the stements are seperate to the the appropriate tables, like...

table 1 = userid , transactionID & card #

table 2 = tansactionID, productID#s, UserID

table one and two, can be on the same page in two seperate forms that post in different proccesses from one trigger in write only privilage.

hidden can be an assembler that has no browser access, or can asseble them after from a script on the admin side the public has no access to...

It's a rough scetch...

bbiab

Paul

[HalfaBee]

You should not put you mysql username and password scripts in php files called by the browser.

I normally put the in a seperate file say dbopen.php and then include that into any scripts that use the DB.

You can then change the permmisions on dbopen to 500.

This is a lot more secure than 755.

you can also put dbopen in a password protected directory for added security.

The CGI-BIN version of php POWWEB uses also stops a lot of problems.

Halfabee

[scottward]

Good idea. Are there any precautions I need to take in addition to saving the username and password in a restricted file?

How's this for a plan:
Create a user with only SELECT, INSERT, UPDATE and DELETE rights. Everytime I connect to the database, whether it is to display product, administer newsletter recipients, or view credit card orders, I will use this user.

I will create a username/password table of my own. When someone tries to go to the administrator section of the web site, I will check the username and password they enter against the list of users in the username/password table I created and use PHP to allow them to view the private data if the info is correct. In other words, I will use PHP to inforce access restrictions instead of MySQL. Will this be safe as long as I store the MySQL username and password in a restricted file and include it into my scripts?

Would you feel comfortable storing customer credit card information in such a system? This solution sounds good to me. Any feedback on the safety of it would be greatly appreciated. Are there any other steps necessary to protect the data? Would encryption be overkill?

Thanks,

Scott

[HalfaBee]

I would encrypt the creditcard info into mysql using there functions.

I think it would be a reansonably safe way to go.

make sure the table names etc are not obvious as well.

eg dont use table.creditcardnumber

this maybe a bit obvious to a hacker!:)

HalfaBee

[scottward]

Can you point me in the right direction as far as encryption with MySQL?

Thanks,

Scott

[TakeIT2]

somthing like what we are talking about already exists for the most part in some of the CMS's as modules you might want to look at a small one frome sourceforge, or one of the other script places, they have some that are done as functions or classes...


paul

[HalfaBee]

Have a look at the php manual http://www.php.net/docs.php

there is a section on mcrypt functions

Good luck