Recommendations for Cleaning a string prior to querying MySQL

[CavemanUK]

Hi,

After getting suggestions recently about my little database. Someone suggestion I look into creating a class for my database and using functions within the class to insert and get data. This has been great and I can definately see the advantages to it. The only thing thats baffling me is the correct way to clean the query im sending to it.

To give you an example,

If I want to search for a name using a form then i might have a line like this...

$customer = $_POST['customername'];
$where = "customername = '.$customer;

$result = dbobject->pullData($where);

i know i can sanitize the $_POST data using mysql_real_escape_string() but this only works when the database link is open which only happens when you call pullData() and obviously I cant sanitize the $where string as this may be made up of a complex query.

I hope im making sense!


I should explain Im very new to the concept of classes etc so some friendly pointers would be greatly appreciated!

 

[Darth.Titan]

Well it seems to me that you should just pass $customer to pullData(), then use mysql_real_escape_string() and create your $where variable there instead of trying to do so outside the method.

I hope that made sense. It did in my head at least.

 

[plinden]

You should learn how to parameterize your queries. NEVER create a query directly from the data. Even if you do it right many times, you're going to allow injected SQL at some point, but parameterizing all queries makes sure you always get it right.

You should look at http://us2.php.net/mysqli

Do something like this:

$name = $__POST['customername'];

$db = new mysqli(”db_host”, “user”, “pass”, “database”);
if ($stmt = $db->prepare(’SELECT customer FROM customers WHERE name = ?’)) {
    $stmt->bind_param(’s’, $name);
    $stmt->execute();
    $stmt->bind_result($customer);
    $stmt->fetch();
}

You can put the fetch code into your class.

 

[CavemanUK]

Well it seems to me that you should just pass $customer to pullData(), then use mysql_real_escape_string() and create your $where variable there instead of trying to do so outside the method.

I hope that made sense. It did in my head at least.

was thinking that. but lets say i want to pull all customers called jones that have a credit limit over £200.00 (this is purely fictional but describes my problem).

my $where would be...

$where = "customername = ".$customer." AND "creditlimit >= 200";

How would I pass that to pullData()?

 

[Cromulent]

was thinking that. but lets say i want to pull all customers called jones that have a credit limit over £200.00 (this is purely fictional but describes my problem).

my $where would be...

$where = "customername = ".$customer." AND "creditlimit >= 200";

How would I pass that to pullData()?

Listen to plinden. You should use parametrised queries.

 

[CavemanUK]

Hadn't heard that term before.. just been having a read and they look interesting but are they the right thing for my site... just to give you an idea.. I work for a very small business and I'm playing around making a closed/private site that a couple of us will use for keeping track of customers and jobs etc...

The data in the database is basically integers, strings and booleans. No binary or anything fancy.

If I understand peramaterized queries, I would make a "template" that can have values passed to it. But the i have a lot of different types of queries and it would involve a LOT of templates. Is this the right way to go?

to give you guys an example.. The homepage has some of the following queries...

$result = $jobobject->getData("status = 73 || status = 20");
$scheduledjobscount = $jobobject->numrows;
	
$result = $jobobject->getData("creationdate = CURDATE()");
$jobsbookedtoday = $jobobject->numrows;

$result = $jobobject->getData("bookingdate = CURDATE() AND staffmember_id=".$staffmember_id);
$myjobstoday = $jobobject->numrows;

It has a load of other ones that query all sorts of things.. my point being.. would it really be the best way to create a lot of templates?

 

[memco]

With parameterized queries, you're getting the best of both worlds: flexibility and security. They are pretty similar to what you have going already, but they are slightly more abstracted and have several significant benefits.

I found PHP PDO to be a very helpful tool in implementing parameterized queries quickly and easily.

Once you pick up how to use PDO, all that is really required is writing a few more functions to separate out the tasks. As an example instead of just doing a raw query for the number of scheduled jobs, you would write a function kind of like this:

 	function scheduledjobscount($codes) {
		 $codes=implode(",",$codes);
			try {
			$stmt = $this->connection->prepare("SELECT * FROM table WHERE status IN (:codes)");

			/*** bind the paramaters ***/
			$stmt->bindParam(':codes', $codes, PDO::PARAM_INT);

			/*** execute the prepared statement ***/
			$stmt->execute();

			/*** fetch the results ***/
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return count($result);
			}
			catch(PDOException $e) {
				echo $e->getMessage();
			}
		}
	}
}

Which you would call like this:

$count=$jobobject->scheduledjobscount($codes);

What's nice about parameterized queries (however you choose to implement them), is that they take care of escaping data for you. You still need to check your data to make sure, for example, numbers are really numbers, etc., but you can do that with your form data before it gets passed to your database.

 

[plinden]

SQL injection is an exploit that allows malicious users to pass in arbitrary queries to the database. So for example, say you have a form that takes a customer name for searching and you craft the query like this:

$query = "select customer from customers where name=".$name;

If you forget to sanitize the $name by calling mysql_real_escape_string, someone typing "; drop table customers" will cause the customers table to be dropped (http://xkcd.com/327/)

Parameterizing your queries will never allow this to happen since, as memco points out, your data is escaped automatically.

 

[CavemanUK]

With parameterized queries, you're getting the best of both worlds: flexibility and security. They are pretty similar to what you have going already, but they are slightly more abstracted and have several significant benefits.

I found PHP PDO to be a very helpful tool in implementing parameterized queries quickly and easily.

Once you pick up how to use PDO, all that is really required is writing a few more functions to separate out the tasks. As an example instead of just doing a raw query for the number of scheduled jobs, you would write a function kind of like this:

Thanks for your suggestions guys, you know a lot more about it than me and what your saying is making sense (i think). to clarify then...

for example,if have 3 different queries i want to execute...

SELECT * FROM Customers WHERE Surname = 'Jones'

SELECT * FROM Jobs WHERE Completed = FALSE

SELECT * FROM Jobs WHERE ServiceLevel < 5

Would I make 3 different Functions or could i make some kind of array to pull in the arguments?