Syntax for special characters in WHERE clause

👨‍💻 Development Help
1

Hi everyone,
I’m getting lost in a problem not strictly related to Suite.

In this WHERE clause of my query

query .= “AND clc_con_cond_cstm.cod_gr_c=’”.$cod_gr_c."’ ";

the variable “cod_gr_c” can also contain an apostrophe or the &, or both. And when there are, the query interrupts.

How can I do the ecape for these characters?

Thanks so much for the support!

2

The db object has a quote method that you can use for this.

Search the code, you will find many examples.

1 Like
3

Hi prg,
Thanks for answer.
The quote method is working fine for the apostrophe but has no effect on the & (if it’s present in the string).

query .= “AND clc_con_cond_cstm.cod_gr_c=’”.$GLOBALS['db']->quote($cod_gr_c.)"’ ";

Are there any suggestions?

EDIT: ok the fact is that in db & becomes & so it doesn’t find the string …

4

Is that & turned into & before the quote function, or in it?

I ask because that looks like an HTML escaping, not an SQL escaping.

SuiteCRM is very messy when it comes to cleaning up text for “security” reasons. It does a lot of over-zealous cleaning. Sometimes your most straight-forward answer is just to use str_replace for that particular character.

I actually have this function in one of my projects :stuck_out_tongue: :

    public static function undoCleanUp($overZealouslyCleanedUpString) {
5

Ok I’m looking for str_replace
but where to make the replacement?

If I do it before the query
$cod_gr_c= str_replace('&', '&', $cod_gr_c);

and in the query I leave

query .= “AND clc_con_cond_cstm.cod_gr_c=’”.$GLOBALS['db']->quote($cod_gr_c)."’ ";

in debug I always read & inside the string while in db it is saved as & (these are all imported fields, maybe it became & for this reason …)

The apostrophe in the string, on the other hand, continues to work fine with quote method.

6

You have to do the replacement after SuiteCRM has changed it for you. I’m not sure when and where that happens, thus the question at the start of my post.

Watch out when examining these things when debugging, if you echo things onto the browser, & will show as &. Make sure you’re looking at something that shows you the contents exactly as they are (like the Watch window in an IDE)

1 Like