How to sanitize simple _GET input from url to mysql in php

Question

I have a simple php script where hopefully people can simply go to something like http://mydomain.com/get.php?id=TEXTMASH-1FD0066D-F75A-5D0C-6784-2FA0D055B0D7 and pull up this text block from my mysql db. I'm very new to php. Knowing that all of expected IDs should be something in the format of TEXTMASH-1FD0066D-F75A-5D0C-6784-2FA0D055B0D7 how can I "sanitize" the _GET so it's not vulnerable to any attacks? I've looked at some guides but, i'm just not getting it. Anyone mind showing me? I'd really appreciate it.

<?php 

include_once("../../extern/dbinclude.php");

$id= $_GET['id'];

$result = mysql_query("SELECT text FROM mytable WHERE id='$id'")
or die(mysql_error());  

$row = mysql_fetch_array($result);

if($row)
{
echo nl2br($row['text']);
} else {
?>
<h2>Invalid URL</h2>
<?php
}

May I ask why you have such complicated IDs? Why not just an INTeger? — Ben G, May 27 '11 at 05:26
Because there will eventually be a TON of entries, and actionscript has a really really simple UIDUtil.createUID() method. — brybam, May 27 '11 at 05:35

score 2 · Accepted Answer · answered May 27 '11 at 05:33

2

You could either use mysql_real_escape_string or possibly a regex to check the input. One possible regex for your case:

$pattern = '/^TEXTMASH-[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}$/';
if(!preg_match($pattern, $id))
{
    echo "No dice >:(";
    exit;
}

answered May 27 '11 at 05:33

Chris Eberle

47,994
12
82
119

Nice. This definitely is a job for a regex, mostly to tell the legitimate user that the ID was not only not found, but was invalid (if you wish). No real need to escape with `mysql_real_escape_string` afterwards, but it won't hurt. – Wesley Murch May 27 '11 at 05:36
@Wesley: well it was more of an either / or thing. – Chris Eberle May 27 '11 at 05:38
I'm sure all the answers are valid, but this seems really simple to implement, gets the job done, and does a good job and making sure its a valid id thanks! – brybam May 27 '11 at 05:42

Ben G · Answer 2 · 2011-05-27T05:34:04.563

Two main aspects to sanitizing the input:

Validate when you get it. Check out the PHP filter functions for doing validation. In your case, the ID is rather complex, and you might need a custom validation function (using regex, possibly) for this. However, I don't see why you're even using such a complex ID when you could use an INTeger ID, as is typical.
Use mysql_real_escape_string() or prepared statements before inserting a user variable into the database.

And whenever you display a string variable, make sure to escape any HTML characters.

score 0 · Answer 3 · edited May 23 '17 at 12:03

By far the best way to do this is through prepared statements. The other answers so far have suggested using mysql_real_escape_string, which works, but is easily missed by a developer and a PITA to have to use all the time. Because it's so easy to miss and so hard to locate those errors, it's not a reliable or maintainable solution.

Prepared statements take this pain out of the process and automatically escape things appropriately.

You can find our more info elsewhere on SO.

How to sanitize simple _GET input from url to mysql in php

3 Answers3