WHERE clause confusion

3 posts by 2 authors in: Forums > CMS Builder
Last Post: July 29, 2009   (RSS)

By MisterJim - July 28, 2009

I'm doing something that is normally, using straight SQL, fairly simple to do.

I've got a table that has a column called 'department'

I'm trying to capture the department name via the URL using a $_GET variable.

So here's the SQL at the top of my page:

$dept = $_GET['dept'];
list($blogRecords, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'allowSearch' => '0',
'where' => 'department = "$dept"',
'orderBy' => 'date DESC'
));

It doesn't work.

YET, if I hard code the variable like so, it works and lists the blog posts within the department: Hair

list($blogRecords, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'allowSearch' => '0',
'where' => 'department = \'Hair\'',
'orderBy' => 'date DESC'
));

Here's the URL: http://www.someURL.com/blog.php?dept=Hair

I've tried various ways to code the WHERE clause, such as:

'where' => 'department = ' . $dept,
returns: MySQL Error: Unknown column 'Hair' in 'where clause'

'where' => 'department = {$dept}',
return: MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}) ORDER BY date DESC' at line 3

'where' => 'department = \'"{$dept}"\'',
returns: No blog posts were found

What am I doing wrong?

Thanks much.

Jim
WebCamp One, LLC



Websites That Work

Re: [Mr Jim] WHERE clause confusion

By Dave - July 29, 2009

Hi Jim,

If you have some comfort with SQL you can see the SQL code being generated by CMSB by adding this option:
'debugSql' => true,

It's really handy to see what's going on and where problems might be.

In this case there's three things to keep in mind:
1) PHP only replaces variables in double quoted strings (not single quoted strings)
2) MySQL requires values be in quoted strings (single or double)
3) You should escape any user form input to avoid SQL injection attacks

Try this:
$escapedDept = mysql_real_escape_string( @$_GET['dept'] );

This will escape quotes in the input (preventing SQL injection) and the @ supresses the "undefined index 'dept'" error that you'd get in the event the link didn't have ?dept= in it.

And this:
'where' => "department = '$escapedDept' ",

Which will replace the variable name with it's value and still have it quoted for MySQL.

Hope that helps! Let me know if that works for you.
Dave Edis - Senior Developer
interactivetools.com