[SOLVED]Security with Databases: Validating Input

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

[SOLVED]Security with Databases: Validating Input

Post 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
Last edited by The Monkey on Wed Oct 20, 2004 10:20 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post 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!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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]."'");

}

?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. :)
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

ignoring that mysql_query doesn't support multiple queries in a single call, it'd fail the query anyways.
  1. blackberry would likely need to be quoted
  2. the other quoted strings would fail because they are escaped by addslahes.
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post by The Monkey »

feyd wrote:ignoring that mysql_query doesn't support multiple queries in a single call, it'd fail the query anyways.
  1. blackberry would likely need to be quoted
  2. 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
Post Reply