Security Concerns

[Mark H]

Hi,As response to a previous topic in which I am gathering form data, and will be using HTML and PHP for this, and then writing the form data into a MySQL database.A friend of mine has suggested that I write code to check for "SQL injection". I am concerned about how security for my MySQL database works: what do I need to do to ensure that only I can access the database? Bear in mind of course that the website needs to access it, but I don't want any other people, or, worse, bots, accessing it.Thanks,Mark.

[jeffman]

Read about sql injection here. It's important to know what you are protecting yourself against.The general solution is mysql_real_escape_string.That may be sufficient for the situation you described. If users are sending data to be stored in the database, other precautions should be taken.

[wirehopper]

You might want to check this out: http://jetscripts.com/sanitizer.htm

[End User]

The general solution is mysql_real_escape_string.

I'm waiting for 'mysql_real_escape_string_no_this_time_we_really_really_mean_it()'. Deirdre's Dad is right- you have to know what you're trying to protect against before you can do anything sensible. Protecting against the wrong kind of threat will only give you a false sense of security, like stocking up on .45 ammo when you actually own a 9mm.

[Mark H]

Thanks all.I have read up on SQL injection. As my database will only have permissions for myself (although the HTML form will write to it) I think the simple escape string may be sufficient.I will also check about sanitizing from your link wirehopper.But a question: if I use selective "hard to guess" names for the database, tables and fields, will this greatly increase my security?Thanks,Mark.P.S. I am presuming that as China can hack into UK Government databases that I am not going to get 100% security!!!!

[Synook]

But a question: if I use selective "hard to guess" names for the database, tables and fields, will this greatly increase my security?

Very slightly. Greatly? No. It will inconvenience you a lot, however, so the gains are probably not worthwhile.

[jeffman]

One kind of hacker may be deterred by hard-to-guess names. But it makes no difference to the automated, brute-force kind of hacker. To this guy, a name like XJ25Q1D is as easy/hard to guess as DEIRDRE.The most effective hacking method anyway is social hacking. Someone calls you on the phone and convinces you they have a legitimate reason for knowing your login data. If you're at work, he might pose as your Systems Manager. He can even get the real manager's name if it's posted on your company website. Now, when he calls up and says there's been a massive crash and he needs to login as you to save your data, you might really get fooled.Or consider the hacker who gets hired on the nighttime cleanup crew. He's got 100% access to everyone's office. You know how many people post usernames and passwords right out in the open? A lot.

[Mark H]

Thanks guys,My main concern is that people will be entering their email addresses and postal codes. I do not want to have their personal data stolen. From what you've said, and I've looked at, the two suggestions you've made should be sufficient. (?)Mark.

[Synook]

Sufficient? Probably not. But it's a good start.I mean, you still have to think about stuff like actual server security (e.g. OS-level and physical security), transmission security (e.g. SSL), social security (e.g. phishing, as DD says), etc. You also should consider contingency methods, such as hashing sensitive data like passwords in your database, so that even if your database is compromised the damage is less.

[End User]

I have read up on SQL injection. As my database will only have permissions for myself (although the HTML form will write to it) I think the simple escape string may be sufficient.

Well...if anything will be writing to the database, then all bets are off. In other words, your database doesn't know it's "you". As long as it gets the correct instructions, it'll happily do whatever it's told to do. It'll just think it's you telling it what to do.

P.S. I am presuming that as China can hack into UK Government databases that I am not going to get 100% security!!!!

It's unlikely, to say the least. On the other hand, the US government is barely able to code its way out of a wet paper bag with a chainsaw in each hand, so you may very well end up with better security than many federal agencies have. And I mean many, not just one or two.

[skaterdav85]

You can also use the mysql improved object (mysqli) and prepared statements for better security and performance rather than the typical way of connecting to mysql where you'd typically concatenate various data items to the query. Using this method of interacting with the DB also involves writing less code.

[Mark H]

Thank you all for your comments.bigdave: I am totally new to using MySQL (except in using pre-coded stuff like forums and wordpress), so I think, at least initially, I shall use the method I am learning in the tutorials.I am also thinking that I will check to see if submitted data is legitimate: e.g. check the Postcode follows the correct format. (I'm only taking submissions from within the UK, so that should be fairly simple.) Of course, I can't really check email addresses, but I can only allow the standard characters (as suggested on the SQL injection article).I also am thinking of using an anti-bot system such as Captcha. This isn't only to prevent unauthorised manipulation of the database, but I also don't want to have a load of bot signatures (probably wouldn't go down well with the Prime Minister if I submitted a bot signed petition!) Question: what would be the best, free, Captcha-type gateway?Thanks,Mark.

[Synook]

http://recaptcha.net/

[Mark H]

Thank you Synook!One further question: for the allowed characters for the email address and the postcode (which should only allow letters and numbers) is there a link you could post from which I can copy the allowed character lists?Thanks,Mark.

[Synook]

There are better ways of validating things than by individual character filtering* - perhaps you may want to have a look at filters and regular expressions?* What the SQL injection article is talking about is the input of MySQL syntax into the database, which is negated not by preventing that insertion but by escaping it (through mysql_real_escape_string()). For example, the character sequence -- is valid in an email address, but is also syntactically significant in MySQL. You wouldn't want to stop people entering that, you just want to stop it from affecting the query.