Converting form data for mysql using php

Question

I have used form data that would be passed to a PHP script that ultimately would store the data in a mySQL database. The data has always been string data. I was wondering if I had data on a form that looks like this:

Age: ( The html page and a text field )

and I have defined age in mySQL as a smallint ( -32768 to 32767 )

How do I do the conversion to make sure age is properly formatted so that it can be placed in successfully in age column in the mySQL database?

I am looking for something like:

$age = mysqli_real_escape_string($con, $_POST['age']);

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('$firstname', '$lastname', '$age')";

I am using http://www.w3schools.com/php/php_mysql_insert.asp as a guide using the db name, table name and field names.

If you're using HTML5 you can specify that the input be type="number. Then use intval() on the server-side to cast the posted info into an integer. — Jay Blanchard
– Jay Blanchard, Commented Jul 29, 2014 at 12:31

Zentoaku · Accepted Answer · 2014-07-29 12:31:03Z

1

Assuming that you already have frontend validation:

(int)$age

if not you can use

is_numeric($age)

before you cast variable to integer

answered Jul 29, 2014 at 12:31

Zentoaku

7674 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d.sergeiev Over a year ago

There are actually many different methods to force numeric type in PHP, but rule of a thumb is: if you cast it to numeric, the worst case is 0/NaN/null, which can not be used as SQL injection.

JimF Over a year ago

I see where you coerce or cast to an integer. But I thought it would be (smallint)$age. I am actually looking at how to cast to a certain data type. Right now I am not concerned with bounds checking. If you look at dev.mysql.com/doc/refman/5.1/en/numeric-types.html I would think for each data type there would be a cast operator of the type (my_new_data_type)$some_input_var. This is really what I am asking. If PHP can do this.

lshas · Accepted Answer · 2014-07-29 12:33:12Z

0

You could use php's intval() function to make sure the only thing that goes into the database is an integer. Examples;

intval("23 years") // 23
intval("23.2") // 23
intval("    23") // 23

http://php.net/manual/en/function.intval.php

answered Jul 29, 2014 at 12:33

lshas

1,7211 gold badge20 silver badges42 bronze badges

Comments

Renjith · Accepted Answer · 2014-07-29 13:13:35Z

0

Please use PHP inbuilt function filter_input.

$ageOptions = array('options' =>
    array('min_range' => 1, 'max_range' => 200));

$age = filter_input(INPUT_POST, 'age', FILTER_VALIDATE_INT, $ageOptions);

If the validation gets passed, value of age will be returned and FALSE if the validation fails.

edited Jul 29, 2014 at 13:13

answered Jul 29, 2014 at 12:58

Renjith

115 bronze badges

Collectives™ on Stack Overflow

Converting form data for mysql using php

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related