0

Possible Duplicate:
Best way to prevent SQL injection in PHP?

My html web form in the php site takes data from the client side and puts it into the mysql database. I was wondering the steps needed to sanitize the data before storing it into mysql.

Assuming this sanitization would need to be performed using PHP and then inserted into Mysql, could you please let me know what all needs to be taken care of?

As a special case, apart from general sanitization, I would want to remove all special characters, spaces, and convert all characters into their lower case before putting into the database. What are the functions that I need to look at for doing this?

I'm quite new to php.

Thanks.

Community
  • 1
  • 1
user1644208
  • 105
  • 5
  • 12
  • Also I can not imagine that the "extra" questions haven't been asked and answered before. Use the search first. – hakre Oct 17 '12 at 18:17

2 Answers2

4

1.mysqli_real_escape_string() or mysql_real_escape_string() to escape quotes

2.use php filter_input for other form data

$search_html = filter_input(INPUT_GET, 'search', FILTER_SANITIZE_SPECIAL_CHARS);
$search_url = filter_input(INPUT_GET, 'search', FILTER_SANITIZE_ENCODED);

There are other options into php manual here is link

http://php.net/manual/en/function.filter-input.php

http://www.php.net/manual/en/filter.filters.sanitize.php

GBD
  • 15,847
  • 2
  • 46
  • 50
3

First of all sanitize input using mysqli_real_escape_string() which will escape quotes in the string.

you can use strtolower() for converting characters to lowercase.

Use htmlspeiclachars() to convert special characters to HTML entities

Use Regular expressions to remove white space preg_replace( '/\s+/', ' ', $whatever );

If you want to remove special characters you can use preg_replace('#[^\w()/.%\-&]#',"",$whatever); regular expression. (Source)

Other sanitization filter reference here

And last but not the least as you are a beginner I would recommend you to refer documentation

Community
  • 1
  • 1
Mr. Alien
  • 153,751
  • 34
  • 298
  • 278
  • I'm tempted to -1 for `htmlentities()` when in most cases `htmlspeiclachars` is the way to go.. – PeeHaa Oct 17 '12 at 18:13
  • well the answer is just misleading and repeats common misconceptions and therefore deserves a -1, not for the single function call but the overall "approach" to the "problem". – hakre Oct 17 '12 at 18:14
  • @PeeHaa Can edit instead of downvoting it? – Mr. Alien Oct 17 '12 at 18:15
  • @hakre misleading in what terms? – Mr. Alien Oct 17 '12 at 18:15
  • Throwing functions on data for nothing. – hakre Oct 17 '12 at 18:16
  • what do you mean throwing functions on data? did you read this `I would want to remove all special characters, spaces, and convert all characters into their lower case before putting into the database.` – Mr. Alien Oct 17 '12 at 18:17
  • Have you asked what the encoding of the string is? If not, your suggestions must have been pre-fixed with a filter first that reduces the input to US-ASCII or even better throws an exception that the precondition has not been matched. Removing duplicate spaces only then to remove all spaces is something that doesn't make much sense in my eyes, too. And all that stuff. – hakre Oct 17 '12 at 18:24
  • 1
    @Mr.Alien Parameterised queries are better than escaping quotes. – MrCode Oct 17 '12 at 18:26
  • @MrCode He is just a beginner... – Mr. Alien Oct 17 '12 at 18:28
  • @Mr.Alien if he is a beginner then that's all the more reason to start using parameterised queries right from the beginning. – MrCode Oct 17 '12 at 18:39