MySQL dynamic search query faills

Queries and Resources
, , , ,
1

Hi, been searching but can not find anything similar.

I am making a form to search in a MySQL database.

So depending on the fields filled on the form, the query is build up by javascript.

The initial query is:

SELECT `id`, `tax_id`, `commercial_name`, `tax_name`, `address`, `locality`, `postal_code`, `province`, `country`, `email`, `telephone1`, `telephone2`, `notes`, `date` 
FROM `clients`;

and it works perfectly.

Now when I add:

WHERE `tax_id` LIKE '%324%'

and the query becomes:

SELECT `id`, `tax_id`, `commercial_name`, `tax_name`, `address`, `locality`, `postal_code`, `province`, `country`, `email`, `telephone1`, `telephone2`, `notes`, `date` 
FROM `clients` WHERE `tax_id` LIKE '%324%';

it should work perfectly, but I get an error saying:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' WHERE `tax_id` LIKE \'%324%\''' at line 3

The query is build like this:

SELECT `id`, `tax_id`, `commercial_name`, `tax_name`, `address`, `locality`, `postal_code`, `province`, `country`, `email`, `telephone1`, `telephone2`, `notes`, `date` 
FROM `clients`{{queryString}};

It seems that problem is the escaping Retool does, but the warning starts from WHERE so is really confusing.
I have been dedicating many hours on this and I cant find a way to advance.
Any suggestion greatly appreciated.

2

After lots of tryes I finaly test by checking the option in the MySQL resource manager to "Disable converting queries to prepared statements"

aaaaaaand it does not work either!

No single quote escaping but query does not return the expected results.

So I go further and make the javascript code to generate the entire mysql query. So now my resource query looks like:

{{queryString}}

instead of the previous one:

SELECT `id`, `tax_id`, `commercial_name`, `tax_name`, `address`, `locality`, `postal_code`, `province`, `country`, `email`, `telephone1`, `telephone2`, `notes`, `date` 
FROM `clients`{{queryString}};

aaaaaaand it works!

It shouldn't be this way, somebody can explain why this happens?

3

Hey @chucktochunk!

This seems like both methods should work with prepared statements disabled as you mentioned. Are you seeing the same error as you were before with the broken one? Would you mind also sharing the code you're using to generate the respective query strings?