Do I need to escape INT type values in Mysql?

Question

I work at a company that's still using mysqli connection. I know that PDO is better and we should be using that but changing to that would require a lot of work that we don't have time for. Our goal is to switch the company website to Laravel sometime in the future.

I know the mysqli class also has a prepare function but it doesn't really fit into the class we made to work with mysqli.

Until then, is mysql going to convert $_POST string variables into INT or DECIMAL types or do I need to use real_escape_string on ALL variables in the sql string regardless of whether or not it's being saved to a string or INT column.

Addon question: Is real_escape_string going to protect me from injections?

Example code:

$sql = "
    INSERT INTO table
    (
         column
    )
    VALUE
    (
         " . $mysqli_connection->real_escape_string($_POST['number']) . "
    )";
$mysqli_connection->query($sql);

score 2 · Answer 1 · answered Jul 27 '15 at 20:18

2

You can use intval() PHP function for integer data before using then with mysql syntax:

$id = intval($id);

any non-numbered posts will result in a number or zero at least.

answered Jul 27 '15 at 20:18

Tariq

2,853
3
22
29

score 0 · Answer 2 · answered Jul 27 '15 at 20:19

0

I would escape or parameterize ANYTHING that comes through $_POST, as you can never guarantee valid values from the client side. Better yet, validate it and the escape/parameterize it.

answered Jul 27 '15 at 20:19

Chris

1,118
8
24

score 0 · Accepted Answer · answered Jul 27 '15 at 20:19

0

No, it won't. Consider submitting this:

1=2

There's no sql metacharacters in there, so it won't be modified AT ALL by the escape function. so you'd end up with

... VALUES (1 = 2)

which would execute as

... VALUES (false)

and end up inserting integer 0 into your table.

answered Jul 27 '15 at 20:19

Marc B

356,200
43
426
500

Choosing as the correct answer because it provides a confirmation that mysql does not convert input to number. – Ethan22 Jul 27 '15 at 20:36
mysql's job isn't to convert anything. it's to do with the best it can with whatever you send it. if you're worried about injection problems, then DON'T roll your own escaping/quoting systems. and especially don't use the mysql_*() functions. use mysqli or PDO with proper placeholder-using prepared statements, and then your question becomes moot – Marc B Jul 27 '15 at 20:38

Do I need to escape INT type values in Mysql?

3 Answers3