Page 1 of 1

Escape an apostrophe, QB handling

PostPosted: Wed May 19, 2021 5:12 pm
by huber
It seems that the Query Builder handles (implicitly) escaping an apostrophe (ll characters to be escaped). As a SQL statement I mask the ' (apostrophe) with utils.stringReplace like in the following example

var query = "\
    SELECT\
        id\
    FROM\
        teaching_allotments\
    WHERE\
        course_code = '" + utils.stringReplace(code, "'", "''") + "'";


As a Query Builder statement, there is (seems to be) no need for masking, like the following code is sufficient. I assume it's escaping the characters described in https://wiki.servoy.com/display/DOCS/Escape+sequences
A confirmation of my assumption would be very helpful. Thanks.

var query = datasources.db.hades.teaching_allotments.createSelect();
var ta = query.columns;
query.result
    .addPk();
query.where
    .add(ta.course_code.eq(code));

Regards,

Re: Escape an apostrophe, QB handling

PostPosted: Fri May 21, 2021 8:45 am
by rgansevles
Robert,

The QueryBuilder translates to sql using prepared statements which saves you from SQL injection attacks, see https://cheatsheetseries.owasp.org/chea ... Sheet.html
So, no escaping needed (if you add quotes they will appear in the data)

Rob

Re: Escape an apostrophe, QB handling

PostPosted: Fri May 21, 2021 7:19 pm
by huber
Hi Rob

Thanks for your answer, good to be sure about the escaping of QB. By the way, for debugging it would be helpful to have the right side of a WHERE clause, not only a ?
But databaseManager.getSQL(query) returns the statement with ? on the right side of the comparison. I assume because of the prepared statement there is no chance to the the real values instead of the ? Is this correct?

Best regards,

Re: Escape an apostrophe, QB handling

PostPosted: Sat May 22, 2021 2:47 pm
by rgansevles
Robert,

databaseManager.getSQL() returns the sql that would run for the current database with a question mark for each parameter.
We also have databaseManager.getSQLParameters() which returns an array of the parameter values in order of the question marks appearing in the sql.

Rob

Re: Escape an apostrophe, QB handling

PostPosted: Tue Jun 01, 2021 12:07 am
by huber
Rob

Thanks for hinting at the Method to get the Parameter values.

Robert