Recommendations for Cleaning a string prior to querying MySQL

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...

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!

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.

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...

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:

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.

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:

Code:

$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.

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:

Click to expand...

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?

MacRumors attracts a broad audience
of both consumers and professionals interested in
the latest technologies and products. We also boast an active community focused on
purchasing decisions and technical aspects of the iPhone, iPod, iPad, and Mac platforms.