PHP (int) for variables in MySQL query. Secure?

Question

I am using following method for MySQL queries:

$sql = "SELECT * FROM mytable WHERE `myTableId`=" . (int)$myId;

Is this a completely safe method or is there a way to inject some sql into the database with this method? Any better alternative?

Marc B · Accepted Answer · 2011-11-09 16:37:34Z

4

It can lead to unintended consequences, e.g.

$myId = 'blahblahblah';

would result in

... WHERE myTableId=0

maybe not such a big deal in this case, but if (say) you're doing a permissions systme and "super-duper-ultra-high-level-user-with-more-power-than-god" has permission level 0, then it's a nice way to bypass security.

answered Nov 9, 2011 at 16:37

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

Your Common Sense Over a year ago

I don't like the example. what prevents the user from entering literal 0?

feeela Over a year ago

@Col. Shrapnel Nothing – and in case of MySQL the first auto-increment value of a column is always 1 (one) to prevent such mistakes…

Levi Hackwith · Accepted Answer · 2011-11-09 16:36:24Z

3

If you truly want to avoid SQL injection, your best bet is to use PDO and prepared statements. check out http://www.php.net/pdo and http://www.php.net/manual/en/pdo.prepare.php

answered Nov 9, 2011 at 16:36

Levi Hackwith

9,31018 gold badges68 silver badges115 bronze badges

2 Comments

feeela Over a year ago

Sure, but do you really need a prepared statement in every case? $database->exec('DELETE FROM user WHERE id = ' . (int)$uid . ';');

Your Common Sense Over a year ago

@feeela so to say - yes. The whole point of prepared statements is make the developer not to thing of the matter yet be safe. And it can be acheived only if used with no exceptions.

feeela · Accepted Answer · 2011-11-09 16:36:38Z

2

Thís should be perfectly save, without any drawbacks, as long as the input can be casted to int.

answered Nov 9, 2011 at 16:36

feeela

30k7 gold badges61 silver badges73 bronze badges

Comments

Hemanta Nandi · Accepted Answer · 2013-03-05 18:35:56Z

0

make it like this

$sql="select `username` from `users` where id='$newid';";
mysql_query($sql);

here $newid is the int value. The symbol used before and after username, to get this you have to press the key just below esc .

answered Mar 5, 2013 at 18:35

Hemanta Nandi

1511 silver badge4 bronze badges

1 Comment

ChrisF Over a year ago

If you see a question where you can post the exact same answer, the correct thing to do is vote or flag the newer question as a duplicate of the older one. It's rare that the same answer is correct for two completely different questions.

Adrian Cornish · Accepted Answer · 2011-11-09 16:40:13Z

-1

I would probably use sprintf instead - but I dont see that it is much different from what you are doing. Placing the integer in quotes may also help.

$sql = sprintf("SELECT * FROM mytable WHERE `myTableId`='%d'", $myId);

Should probably add that you may want to deal with the case when conversion to integer fails. So dont have a table zero.

answered Nov 9, 2011 at 16:40

Adrian Cornish

24k13 gold badges64 silver badges81 bronze badges

4 Comments

Your Common Sense Over a year ago

what does it mean - "Placing the integer in quotes may also help"? How it can help alone?

Adrian Cornish Over a year ago

Because then it gets passed as a string to mysql rather than an integer or possible a special character that could be used for injection

Adrian Cornish Over a year ago

Did I say that just adding quotes magically protects you from all sql injection? No I dont think I did!

Your Common Sense Over a year ago

I think you did. As you didn't say anything else.

Leysam Rosario · Accepted Answer · 2011-11-09 21:29:26Z

-2

No need for the Int if you are just worrying about the mysql injection. To prevent mysql injection you can use mysql_real_escape_string.

What you have right now will block all mysql injection if your mysql condition is only for int but if the situation is like this:

$username = $_GET["username"];
SELECT * FROM customers WHERE username = '$username'

if the $username value is *\' OR 1* your in trouble or i should say your dead

if the $username value is *\'; DELETE FROM customers WHERE 1 or username = * your very dead + doomed

To prevent this from happening use mysql_real_escape_string

$username = mysql_real_escape_string($_GET["username"]);

answered Nov 9, 2011 at 21:29

Leysam Rosario

3692 silver badges11 bronze badges

12 Comments

Your Common Sense Over a year ago

oh. that grave delusion again. mysql_real_escape_string is not for preventing injections. and your answer WILL lead to injection.

Leysam Rosario Over a year ago

Plese read this first before you say some accusations php.net/mysql_real_escape_string

Leysam Rosario Over a year ago

Or at least google or research first? Or you could explain me why mysql_real_escape_string will not block injection. Then I will accept that I am wrong.

Your Common Sense Over a year ago

if the OP will do what you say, $myId = mysql_real_escape_string($myId); he'd got the query vulnerable to sql injection. So, in your place I wouldn't say that

"No need for the Int if you are just worrying about the mysql injection. To prevent mysql injection you can use mysql_real_escape_string."

. For the explanation you can refer to the links you posted.

Leysam Rosario Over a year ago

I see. Thank you guys. I learn a lot here :D Im just 17 anyway there is more for me to learn.

|

Collectives™ on Stack Overflow

PHP (int) for variables in MySQL query. Secure?

6 Answers 6

2 Comments

2 Comments

Comments

1 Comment

4 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

2 Comments

Comments

1 Comment

4 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related