Converting a string to a mySql DECIMAL type

Question

I am trying to insert data about an item's price from an HTML form into a mySQL database. The input field is defined as follows:

<input type="text" name="price" value="0.00"/>

The form is POSTed to the next page in which the database stuff is taken care of. Currently I just enter the exact contents of $_POST['price'] into the database field, which has type DECIMAL(4,2). I heard that this was stored as a string but the database throws an error whenever I try and do this. Is there a PHP function for converting between strings and the MySQL DECIMAL type? Or will I have to do some formatting myself?

Answer 1

You should never just "enter the exact contents of $_POST['...']" into any database field : it's a door opened to SQL Injections.

Instead, you must make sure the data you are injection into your SQL queries are actually valid, according to the expected DB datatypes.

For decimals, a solution, on the PHP side, would be to use the floatval function :

$clean_price = floatval($_POST['price']);
$query = "insert into your_table (price, ...) values ($clean_price, ...)"
if (mysql_query($query)) {
    // success
} else {
    echo mysql_error();   // To help, while testing
}

Note that I didn't put any quote arround the value ;-)