php form validation with mysql

Hi all,

Is there any up to date information with regards validating and sanitising form data in PHP. The data will be stored in a mysql database.

I tend to use htmlentities and mysql_real_escape_string but have read various sites, some old, some new that suggest these methods are not entireley flawless and was wondering if there were better methods of checking user input data.

The types of data I would be looking at would require removing special characters from strings, verifying numeric input and reducing the risk of mysql injection.

I'm using php 5.3
LVL 19
joolsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
The mantra is "Accept only known good values."  Regular expressions and the filter_var() functions are what the professionals would use.
0
Marco GasiFreelancerCommented:
I suggest to refer directly to php.net: http://php.net/manual/en/security.database.sql-injection.php

Here you find all useul informations. The security in php is hard, but php.net is the best source to refer to for updated info about his and other questions.

Cheers
0
Ray PaseurCommented:
SQL injection is certainly one risk, but hardly the only one.  Let's look at these: removing special characters from strings, verifying numeric input and reducing the risk of mysql injection.

Removing special characters is the wrong approach.  You do not want to remove bad values; you want to accept only known good values.  If you had to guess at all of the bad values, you would be at terrible risk that you might have overlooked one.

Verifying numeric input.   Again, check the data to see if it matches the expected data type and if it is in the expected range of allowable values.  Then you will know whether it is good or not.

Reducing the risk of SQL injection can be accomplished with this function.  Use as directed in the PHP.net man page.
http://php.net/manual/en/function.mysql-real-escape-string.php

There is more on PHP.net.  Follow all the links here to get a good overview.
http://php.net/manual/en/security.php

You might also want to make a Google search for "PHP Security" and read the top ten recommendations.  Do that once a month, as good practice for continuing education!

best regards, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

joolsAuthor Commented:
Thx for the posts, I'd been looking at the filter options but had also found sites like this;
   http://stackoverflow.com/questions/129677/whats-the-best-method-for-sanitizing-user-input-with-php

Most of the articles I read today (been googling for over four hours) seem to contradict each other, on one hand there are the people who favour the builtin filter options then there are others which say the filter options are useless and to stick with htmlentities, mysql_real_excape_string et al. This I put down to programmer preference and the possibility that the filter options may have been new features (at the time the articles were published) and not that mature when it comes to options.

I'm more famialiar with the htmlentities etc functions and have dabbled in the regex/preg_replace nightmare for sanitising some string inputs but want to be sure I keep my knowledge as up to date as possible and learn as much as I can along the way.

I wasnt expecting a single answer to what is a quite large subject but I was hoping to find some more up to date articles with real world example code.
0
joolsAuthor Commented:
Thanks for the update Ray, you must be a quicker typist than me :-)

I'll check out the links and info you posted and get back to you tomorrow. I'll leave the Q open in the hope of getting more points of view.

Cheers All.
0
MichaelT_Commented:
Ray has covered a lot, however just to reiterate:

1) Use regex where you need to limit what is being entered i.e check a date is a date, a phone number is a phone number.  Use filter_var to cover some scenarios (prevent "reinventing the wheel" so to speak).  Some cases you can't really restrict what people are entering i.e a

2) Use mysql_real_escape_string on data that is going into the database, as well as htmlspecialchars or even strip_tags if you don't want to allow any html tags (you can specify a white-list for strip_tags, can be handy to allow <br />).  Although you won't cover all scenarios (i.e hex values) these functions will help a lot

3) Use PDO or some sort of similar data access layer. Using prepared statements reduces the risk of inserting unsanitized input into the db, dramatically reducing the risk of sql injection.

4) Only give your database the permissions it needs to perform its tasks.  It's unlikely that you would need to drop any tables, so create a user with read permissions (your application may need update and delete too) and only connect to the db in your application with that user.

Obviously there is plenty more, but the main thing is that you are proactive about it, you will learn a lot by both reading and implementing, just remember that there is no 'one size fits all approach' hence why on forums etc you get differening opinions. Read, take stock and implement what you think is best for your application then readjust as you learn more.

All the best,

Michael.

Michael.
0
joolsAuthor Commented:
Thanks for your views on what is a particularly large subject, it certainly reaffirmed some of my original thoughts and also gave me something else to think about.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.