"IN" SQL Condition with db_query

By ethanw on 5 Oct 2006 at 16:47 UTC

Hi All,

I'm working on a 4.7 site and am hacking the event_block_upcoming function in the event module to make an event_block_upcoming_of_type function that uses an "IN" SQL statement to filter out all blocks but those of a certain type. I tried building the list for the in as a string and then inserting it into the query using %s, but _db_query_callback escaped the single quotes in the list. Is there another way to build IN condition lists with db_query. It would be great if I could pass db_query an array and perhaps a %L flag and have it build the list for me and escape the contents. Here's an (untested) stab I took at hacking _db_query_callback, adding this case to the switch (and modifying the regexp constant to include %L):

		case '%L':
			$list = array_shift($args);
			$escaped_list = array();
			foreach ($list as $list_item) {
				$escaped_list[] = db_escape_string($list_item);
			}
			$list_sql = "('" . implode("','",$escaped_list) . "')";
			return $list_sql;

anyone have any thoughts about that route?

I apologize if this qualifies as a support instead of development question or has been addressed before (it's hard to search for something involving the word "IN";-).

Thanks,

Ethan

Comments

Posting what you are doing would help to answer your question

nevets commented 5 October 2006 at 17:21

Lacking what your code does (not the 'patch' posted) I can tell you if I do something like this

$exclude = array('a', 'b', 'c');
$sql = "SELECT .... WHERE field NOT IN (%s)";
$results = db_query($sql, implode(', ', $exclude);

that it works.

Generating "IN (...)" Condition SQL with db_query

ethanw commented 5 October 2006 at 20:05

Nevets,

Thanks for your suggestion, and my apologies for not providing enough context. What I'm trying to do is construct a function I can use that will return a themed list of all upcoming events of a certain type. I don't want to use Views because the queries it's executing are taking too long and I can't filter dynamically as I'd need to in order to display only events after the current date.

Instead, I saw that a fairly simple modification to events_block_upcoming could yeild and events_block_upcoming_type function where I could pass the types of node I wanted in the upcoming events block to the function and have them appended to the where clause using an in statement. When I tried to do this, however, the single quotes in the in statement were escaped, breaking the IN condition syntax.

To fix this I added a "%L" operator to the list of substituted strings in the db_query_callback function and the DB_QUERY_REGEXP constant. The code for the case statement associated with this string takes an array as an argument, escapes each string of the array, then concatenates them, adding the single quotes to each element in the list. So, for example,

array("I'm","A","List") becomes
array("I\'m","A","List") which becomes
"('I\'m','A','List')"

I tried your suggestion, but it generated a string without the elements of the list enclosed in single quotes and that didn't work on my setup. The SQL it generated was like this: "SELECT ... WHERE field IN (option1,option2,option3)" where MySQL is looking for "SELECT ... WHERE field IN ('option1','option2','option3'). Perhaps we're using different databases? (I'm using MySQL 4.1, with PHP5 on OS X)

So my basic problem is that I want single quotes in a query string that are not escaped, but %s escapes them. Is there a better way to "sneak" single quotes into db string while still escaping potentially dangerous content (eg. not building the IN condition into the $sql variable to bypass the escaping)?

Also, I don't know about the cross-database issues relating to the IN condition. I wouldn't be surprised if MySQLs syntax was non-standard. Still, this would be a big help for me.

Thanks,

Ethan

--
ethan winn
http://colab.coop