Database security

[willpower]

Have a shopping cart DB

 

I originally only gave Select and Insert rights to the 'cart' user. As my application developed I realised I needed to grant additional rights ie UPDATE so as orders could be updated and finally DELETE so as orders could be removed.

 

NOW

 

Clearly this opens me to major vunerabilities.

 

I wanted to have you all share your thoughts on the following.

 

a) How do you , in a similar situation, grant user access to DB's and

b) add functions to your form data handling to prevent SQL injection.

[dsaba]

this is helpful

 

http://templora.com/content/14

[per1os]

The best way to prevent sql injection is to mysql_real_escape_string() anything that comes from outside before it is put into the db.

 

As for the privileges, I have never had the need to limit in my php applications because when I code them I make sure that the delete functions require a valid administrator. So for say you have a function called deleteRow, this is how I would set it up:

 

<?php
function deleteRow($rowID) {
      if (!isAdmin()) {
            die("You are not an Administrator!");
      }

      mysql_query("DELETE FROM table_name WHERE rowid = '".mysql_real_escape_string($rowID)."' LIMIT 1") or DIE("Could not delete row: " . mysql_error());

      return true;
}
?>

 

Hope that helps.

 

--FrosT

[willpower]

thanks for the info. the admin thing aint relevant unfortunately...as these are shoppers which have to administrate their own DB in essence. Does that make sense?

 

 

[per1os]

It sounds like there needs to be a user authentication system in place if you ask me. But yea I do not know the full story =)

 

Have fun with it!

 

--FrosT

[willpower]

There is a login function, however the client wanted users to be able to buy, with or without login.

 

So ulitmately any tom dick or harry can come along and place an order.  My concern is when the go to complete an order they have to enter their billing/shipping addresses.  Now These are stored in a table against a guestid that has ben assigned to them (in the background) to suuport their cart fucntionality.

 

It is this input of addresses etc that is where my vunerability lies.  And it is here that I really need all the help I can get.Massive product tables and high value items.  I dont want some 13yrs old injecting a DROP table on me or worse!!!

 

 

Thanks all for reading....I appreciate all thoughts on this one!

[per1os]

As long as you use that mysql_real_escape_string, you should be fine. If you are really worried you can do checks for that. But yea, see the website posted above. That explains the sql injection perfectly. As long as you follow those guidelines you have nothing to fear. That and make sure that guest users do not have access to a delete function in the code or something ridiculous like that.

 

--FrosT

[willpower]

SO do we all concur that EVEN with a user having DELETE rights to the table...if I use

 

myql_real_escape_string()

 

I'll be ok?????

 

 

[per1os]

If you can drop any table from my site using any form, I will say that I am wrong and you screwed me over.

 

www.aeonity.com

 

Feel free to try a SQL Injection.

 

--FrosT

[Glyde]

Out of curiosity...why exactly do you need to store a guest user's address and information in a database.  This is highly insecure.  First of all, what is the point.  If a new guest_id is assigned each time a guest accesses the site, then when a user that has already placed an order comes back and gets a new guest id, they won't have their saved information.  Also, if you do in fact do some check such as an IP check, what about schools or businesses behind a router.  Overall, saving any sort of private information for a guest (ESPECIALLY when stored in a DB) is impractical.  If you really want to store user data for a guest, my suggestion is to use a cookie and set that WITH AND ONLY WITH the consent of the shopper.  Anything else is risking security vulnerabilities (not in the SQL injection, but rather in invasion of privacy) if your script sends the wrong information to a guest that you thought had visited before, but really has not.

[willpower]

I think that my point is being lost here. So firstly i'd like to reiterate it...then respond to the last post.

 

My question is how do you protect the situation im in....not how do i start again.

 

In response to Glydes comments:::

 

no one said anything about storing a guest users address in the DB...until the order is being processed. So the guest id is simply a sesion var that is set and tracks orders places in a cartdb against that user.  if they proceed to checkout they HAVE to enter their billing and shipping.  This is then stored and HAS to be for back office purposes.

 

The question is how do i prevent SQL injection and attacks such like, not about confirmation to current Data Protection Laws in the UK.  Which, incidentaly, the site does conform too.

 

 

[per1os]

If that is it willpower, my answer should be sufficient no?

 

The mysql_real_escape_string works great for the sql injection.

 

If you are worried about the js exploit, that can be done pretty easily with an str_replace statement

 

Either way you should be safe as long as you do escape the data you are putting into mysql.

 

--FrosT

[Glyde]

I misunderstood you, and I apologize.  I thought you were putting the user address in the database as a method of saving information, as that point was made unclear.  As for what you would like to do, as stated before, a mysql_real_escape_string() for each object inserted into, selected from, or updated in a DB should do the trick.  I can't imagine a way in which, while using mysql_real_escape_string, you could run a DROP command.  I could see something like: SELECT * FROM tablename WHERE field='userdata' becoming:

SELECT * FROM tablename WHERE field=''; DROP TABLE tablename;.  However, 1) I'm not sure it'll even run both queries through one single mysql_query() call (don't quote me), and 2) mysql_real_escape_string would make that query into:

SELECT * FROM tablename WHERE field='''; DROP TABLE tablename;' which would just make SQL search tablename where the field is '; DROP TABLE tablename;, and would not actually drop the table.  (In case you didn't know, two single quotes: '' is a way of escaping a single quote in MySQL.  When calling an insert with something such as: O''Reily, it would be put into the DB as O'Reily).

[willpower]

Thanks all for your input....I may sleep a little easier tonight.