MariaDB system.db.runPrepQuery/runPrepUpdate syntax errors

General Discussion
1

Hi All,

I hope someone can offer some advice as to what I am doing wrong in some scripting.

Before I go on, please note that numbers denoted "v" are variables, "ph" are placeholders in the "".format() method and "c" represents db columns as usual. And this script is being used to take input from a text field and filter the rows, more or less just a search utility.

To begin with I tried:

system.db.runPrepQuery("SELECT `c1` FROM table WHERE `c1` LIKE %?%", [v1])

but this method returned a SQL Syntax error. The only work around I have found is using:

v2= "%{ph1}%".format(ph1=v1)

system.db.runPrepQuery("SELECT `c1` FROM table WHERE `c1` LIKE ?", [v2])

if anyone can offer any advice to why the top line produced a SQL Syntax Error and the bottom did not please let me know.

Thanks all in advanced for your help.

MG.

2

Parameterization includes quotes for values that needs quotes. (Not really, under the hood, but that is the effect.) So the above became
LIKE %'something'% when assembled.

If you don't want to assemble the percent signs into the parameter, your SQL needs to properly assemble the string, something like this:

LIKE CONCAT('%', ?, '%')
2 Likes
3

Side note: You should avoid using the back-tick punctuation for MariaDB identifiers. It supports the SQL standard (double quote marks) for identifiers, making your SQL portable to other brands. (Same with MS SQL Server: it can use standard double quotes instead of square brackets.)

2 Likes