Page 1 of 1
[SOLVED]Security with Databases: Validating Input
Posted: Wed Oct 20, 2004 7:39 pm
by The Monkey
Hello there,
When doing a database query with user-inputted data, how would I go about making sure the data is not ment as a hax0r, via
Sql-Injection [php.net]?
For instance:
Code: Select all
$sql = "SELECT * FROM users WHERE username = $user LIMIT 1";
Now, according to the sql-injection article, someone could query my database however they wanted when doing a username lookup: For instance, instead of $user being a valid username, it would be code meant to compromise my database.
What I do not understand from the sql-injection article is how to confirm the inputted data! For instance, we aren't looking at using an integer: It's a
username, so we can't use [php_man]is_int()[/php_man].
More than likely, we are looking at something using preg_match or such, to remove the possibilities of comments / etc in the sql query, but what are your takes on this problem?
Note to moderators: Feel free to move this to "Database" if you feel like it is more relevant to the discussion there.
- Monkey
Posted: Wed Oct 20, 2004 7:52 pm
by John Cartwright
Why not query the userid?
If not, you can check the username for invalid characters
for example, no whitespaces, letters and numbers ONLY.
And yes, preg_match is what I would do when validating usernames
Posted: Wed Oct 20, 2004 8:01 pm
by The Monkey
I'm sorry I did not make that clear, I was going to have a box that allowed a client to type a user's name (such like phpBB2, but I was unable to find the function that made sure the user-inputted data was not intended to compromise the database), and have the script return data based on what the user inputted. In such a case, having the user input a user_id is impracticle.
However, yes, you did make how I should use preg_match clear; although whitespaces in usernames are useful, I see no reason why they should have characters other than a-z0-9. Kinda tough to comment something out with only alphanumeric characters...
Thanks!
Posted: Wed Oct 20, 2004 8:20 pm
by John Cartwright
Code: Select all
<?php
if (preg_match("[^A-Za-z0-9]",$_POST['username'],$match))
{
$result = mysql_query("SELECT * FROM `users` WHERE `username` = '".$match[0]."'");
}
?>
Posted: Wed Oct 20, 2004 9:28 pm
by feyd
phpbb preprocesses the input data before it gets to the "meat" of a page script. It's in common.php.. basically, they [php_man]addslashes[/php_man] everything to make it more Kosher.

Posted: Wed Oct 20, 2004 10:00 pm
by The Monkey
feyd wrote:phpbb preprocesses the input data before it gets to the "meat" of a page script. It's in common.php.. basically, they [php_man]addslashes[/php_man] everything to make it more Kosher.

I'm not sure I understand how addslashes helps in this case.
For instance:
Code: Select all
<?PHP
$toppings = "blackberry; UPDATE users SET admin = 'true' WHERE username = 'leet hax0r' LIMIT 1");
$sql = mysql_query("I-NSERT INTO pie SET toppings = $toppings");
?>
Of course, above example could be easily circumvented by simply making sure that only alphanumeric characters exist in string $toppings, yet what am I missing? What would addslashes do to said example?
- Monkey
Posted: Wed Oct 20, 2004 10:10 pm
by feyd
ignoring that mysql_query doesn't support multiple queries in a single call, it'd fail the query anyways.
- blackberry would likely need to be quoted
- the other quoted strings would fail because they are escaped by addslahes.
Posted: Wed Oct 20, 2004 10:17 pm
by The Monkey
feyd wrote:ignoring that mysql_query doesn't support multiple queries in a single call, it'd fail the query anyways.
- blackberry would likely need to be quoted
- the other quoted strings would fail because they are escaped by addslahes.
Ok, I understand now. Thanks guys!
- Monkey, finally sure his database won't be hax0red