Best way to Cleanse Data

PHP
1

Hi all, wondering what you guys opinion is on cleansing and validating data from a form to enter the database. This is how I’m currently entering data (no validation, though it’s not yet live). Any advice is greatly appreciated as always :slight_smile:

//Push Data into Database					
			$sql = "INSERT INTO order_products (receipt_id, customer_id, product_name, product_price, product_quant, product_serial, product_code, product_cond, sub_total) VALUES ('$_POST[receiptno]','$_GET[id]','$product[name]','$blip','$product[quantity]','$product[serial]','$product[stock]', '$product[cond]', '$total')";
			mysql_query($sql);
			 }
			 $addDet = "INSERT INTO orders (addedby, receipt_id, date_added, del_method, courier, spec_instr, cust_ref, date_dispatched, delivery_costs, part_exchange, vat_info, three_month_warranty, pay_method, delivery_address, deladd_name) VALUES ('$_SESSION[SESS_LOGIN]','$_POST[receiptno]',now(),'$_POST[delmethod]', '$_POST[courier]', '$_POST[specialinst]','$_GET[id]', '$_POST[dispatched]', '$postage', '$px', '$_POST[vatInfo]', '$_POST[warranty]', '$_POST[payment]', '$_POST[cdeladdress]', '$_POST[deladdressname]')";
			 mysql_query($addDet);
2

You’re using mysql, so apart from all field specific validations (for example: price having two decimals, three month warranty having a valid value (Y/N), etc), every string (that is every value you put between single quotes in your query) must be passed through mysql_real_escape_string, and every numeric value must be tested to be numeric.

3

Hey dude, thank you for your reply. So would a mysql_real_escape_string suffice? Would that give a good enough level of security? What would be the best way to implement this - by creating a function to run through each $_POST or something?

4

As far as preventing sql injection goes, it should be enough to pass strings through mysql_real_escape_string (where, as I said before, “strings” are all user input values you put between quotes in your query, even if in reality they are numeric values).

All user input values you don’t put between quotes in your query have to be checked to contain allowed values.

5

You’d be better using mylsqi or [URL=“http://php.net/manual/en/book.pdo.php”]PDO to benefit from prepared queries with mysql. And if your hosting service provides php 5.2 or better, using [URL=“http://php.net/manual/en/function.filter-input.php”]filter_input to get your $_POST, $_GET or $_COOKIE data would be a good thing.

Another thing to be aware of is character encoding which may open you to some exploit-from-space : http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html

6

Hey guys, I’ll have a play and see what I can do. Not sure on the sure of filter_input. I’ve read some to the comments on the PHP Reference Site, many recommend not using it. Probably best to stick with using mysql_real_escape_string.

7

I explained mysql_real_escape_string because in the code you posted you’re using mysql_query. But if everything is still open for discussion, then do take a look at PDO.

8

The easiest way to think about this is that PHP has the role of a) catching data as it arrives from the web and then b) passing it onto something else.

In your case it is passing it onto your database.

The rule is called FIEO, Filter Input, and Escape Output.

Make sure that when PHP catches data it only allows in what is expected, it Filters that input.

eg if you are expecting a date in the format yyyy-mm-dd then if that string contains only numbers and dashes and is exactly 10 chars long, and you reject anything else, then you could be said to be Filtering Input.

You could go on to checkdate() to make sure it is a real date, and you could decide to abort the whole operaton or send a helpful message back to the user.

Now, when PHP does its second job, ie prepares the data to be stored or displayed somewhere else, then it has given up trying to filter the data any more and now makes sure that the output cannot be permitted to do any damage to the next place it is going to, be that a database, a webpage, pdf file or xml file etc.

eg use mysql_escape_string( $input ) before putting it into a database.

FIEO.