Escape an apostrophe, QB handling

Questions and answers regarding general SQL and backend databases

Escape an apostrophe, QB handling

Postby huber » Wed May 19, 2021 5:12 pm

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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 403
Joined: Mon May 14, 2012 11:31 pm

Re: Escape an apostrophe, QB handling

Postby rgansevles » Fri May 21, 2021 8:45 am

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1907
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Escape an apostrophe, QB handling

Postby huber » Fri May 21, 2021 7:19 pm

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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 403
Joined: Mon May 14, 2012 11:31 pm

Re: Escape an apostrophe, QB handling

Postby rgansevles » Sat May 22, 2021 2:47 pm

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1907
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Escape an apostrophe, QB handling

Postby huber » Tue Jun 01, 2021 12:07 am

Rob

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

Robert
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 403
Joined: Mon May 14, 2012 11:31 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 4 guests