parentheses become encoded in db query

[eluser]SailorJohn[/eluser]
I have a legacy database with a field which contains some parentheses,

|field1|
|------|
|red(b)|

But if I try to query for that value, the parentheses come out encoded.

This query:

Code:

select * from table where field1 = "red(b)"

Becomes this query:

Code:

select * from table where field1 = "red & #40; b & #41; "

(I put a space between the & and #).

This return no rows.

Any ideas on how to handle this?

[eluser]KingSkippus[/eluser]
Try single quotes:

Code:

select * from table where field1 = 'red(b)'

[eluser]SailorJohn[/eluser]
With single or double quotes doesn't seem to make a difference - either way, the parentheses become encoded. If you echo $this->db->last_query() and then view source, you will see this.

There must be a way to handle this.

[eluser]KingSkippus[/eluser]
Dumb question, but what exactly is the code that you are using to run the query on the database?

[eluser]SailorJohn[/eluser]
Well, it's a MySQL database query. The actual query had four left joins, but the problem seems to be the encoding of parentheses. On a test page, I set it up like this:

$sql = 'select * from table where field1 = "red(b)"';

$q = $this->db->query($sql);
//echo $this->db->last_query();

if ($q->num_rows() > 0) {
foreach($q->result() as $row) {
$data[]=$row;
}
}

(When I enable "echo $this->db->last_query()", I can see the encoded parentheses).

Am I missing something?

[eluser]KingSkippus[/eluser]
Again, please use single quotes for string literals. Even if it works in MySQL, it doesn't necessarily work in other databases, it's not standard SQL, and I personally guarantee you that it will save you grief in some form or another if you change now. I don't mean to sound preachy, but I can't tell you how many times I've run across code that breaks because the user coded something non-standard at the time that later became deprecated.

That having been said, I don't know what the issue is with this particular question. I've written that exact query on my server and it works fine, with the parentheses unescaped. (Yes, with single and double quotes.) I did notice in the above code that your sample uses "table" as the table name, which isn't allowed in MySQL and will throw an error 1064 for using a reserved word as your table name. If your table name is literally table, you will need to escape it in backticks:

Code:

$sql = 'select * from `table` where field1 = \'red(b)\'';

For hopefully obvious reasons, I'd highly recommend against using a table named `table` in your database.

Other than that, are you absolutely, positively certain that your test code is exactly as it appears above, that you're not generating the query some other way? One thing that might be worthwhile to try is to make sure $sql is what you really, really think it is by inserting this test code and verifying that you're really passing parentheses and not html-escaped parentheses:

Code:

echo htmlentities($sql);

Oh, another dumb question. You are using MySQL, right? I've been assuming that, but really, without asking, I don't know for sure.

[eluser]SailorJohn[/eluser]
OK, as it turned out, the encoding was taking place between routes.php and my controller, so all I had to do was remove the encoding before running the query.

Working great now.

[eluser]KingSkippus[/eluser]
Awesome, I didn't want to have to scratch my head again.