Syntax for WHERE with strin Criteria?

Classic Servoy
1

I’ve used arguments exclusively to generate the correct SQL string for getting a dataset - to date. Mainly because there have been occassions when I just needed a single criteria in the ‘where’ clause and after putting that in a var I’ve struggled to get the ’ "’ " right for it to be seen as criteria.

Some help I was given today on setting valuelists prompted me to use this: var query = ‘SELECT * FROM mechanism_library WHERE cc_mech_type = "’ + mech_type + “”;

This was not acceptable and I tried allways to format the sequence of quotes to get my variable into the string, to no avail! When the text in mech_type was recognised (thru debug review) the getDataSetByQuery thought it was looking for a column by the name of the text in mech_type (‘Prevention’)!

What is the correct syntax for using a variable in the SQL string without passing arguments (that is just adding a variable to the string)?

2

Always use a prepared statement with arguments: it’s faster and saver.

Paul

3

pbakker:
Always use a prepared statement with arguments: it’s faster and saver.

Paul

Thanks for that Paul - I’d still liketo understand the mechanics of those “quotes” though?

4

Hi Ian,

if you want to work without the variables, this should do the job:

var query = 'SELECT * FROM mechanism_library WHERE cc_mech_type = \' ' + mech_type + ' \' ';

or

var query = "SELECT * FROM mechanism_library WHERE cc_mech_type = ' " + mech_type + " ' "

So: or use single quotes and escape () te ones that are real, or use double quotes and you just type the single ones where you need them.

But as Paul already mentioned, it’s much easier, faster & saver to use ? at the place of the variable. And then pass the variable.

Hope this helps!

5

mboegem:
Hi Ian,

if you want to work without the variables, this should do the job:

var query = 'SELECT * FROM mechanism_library WHERE cc_mech_type = \' ' + mech_type + ' \' ';

or

var query = "SELECT * FROM mechanism_library WHERE cc_mech_type = ' " + mech_type + " ' "

So: or use single quotes and escape () te ones that are real, or use double quotes and you just type the single ones where you need them.

But as Paul already mentioned, it’s much easier, faster & saver to use ? at the place of the variable. And then pass the variable.

Hope this helps!

Again Marc - thanks Bud. Not sure why using qualified args is safer but that does clarify the syntax for me.

Cheers

6

Hi,

Look at this : SQL injection - Wikipedia
Regards,

Hans

7

Hans Nieuwenhuis:
Hi,

Look at this : SQL injection - Wikipedia
Regards,

Hans

Thanks Hans - I was aware of that challenge - but I was stupidly thinking small about my current situation where I create the variable content in code - its not user input.

Good ‘heads-up’ though thanks.

8

Prepared statements are also faster in execution on the database, because the database only has to figure out once how to best execute the query.

As long as the SQL of the prepared statements is the same, the DB will think of it as the same statement, regardless the value of the arguments.

If you paste the values into the SQL string, then each query will be a different statement to the database and thus the database needs to figure out again how to best execute the query.

So in short: use prepared statements! :-) It’s saver, faster and easier to write and maintain.

Paul

9

pbakker:
Prepared statements are also faster in execution on the database, because the database only has to figure out once how to best execute the query.

As long as the SQL of the prepared statements is the same, the DB will think of it as the same statement, regardless the value of the arguments.

If you paste the values into the SQL string, then each query will be a different statement to the database and thus the database needs to figure out again how to best execute the query.

So in short: use prepared statements! :-) It’s saver, faster and easier to write and maintain.

Paul

Tanks for the explanation Paul .