Harden search against SQL injection #356

Merged

p3k merged 7 commits from 355-harden-search-against-sql-injection into main

2023-03-05 14:33:16 +00:00

p3k commented

2023-03-03 17:02:06 +00:00

(Migrated from github.com)

Introduce option to enable prepared statemtents in Sql library code
Add new method Sql.prepare() to manage java.sql.PreparedStatement objects
Modify search query to support a prepared statement accordingly
Use Sql.prepare() to modifiy the search query
Apply newly introduced Helma method DatabaseObject.executePreparedRetrieval()
Process result as used to be

Example code.

const sql = new Sql({prepared: true});
const query = "select * from foobar where foo = ?";
sql.retrieve(query, 23);
sql.traverse(() => { /* … */ });

* Introduce option to enable prepared statemtents in `Sql` library code * Add new method `Sql.prepare()` to manage `java.sql.PreparedStatement` objects * Modify search query to support a prepared statement accordingly * Use `Sql.prepare()` to modifiy the search query * Apply newly introduced Helma method `DatabaseObject.executePreparedRetrieval()` * Process result as used to be Example code. ```js const sql = new Sql({prepared: true}); const query = "select * from foobar where foo = ?"; sql.retrieve(query, 23); sql.traverse(() => { /* … */ }); ```

p3k commented

2023-03-05 12:38:46 +00:00

(Migrated from github.com)

The easy route actually was a dead-end: casting the PreparedStatement object as a string does not reliably return the desired result (i.e. the actual SQL query).

This is due to different DB drivers behaving differently (some compiling the SQL beforehand, some others don’t) and in case of MariaDB we get something like this:

sql : 'select comment.id from content as comment, content as story, site, metadata, account as creator, account as modifier where site.id = ? and comment.prototype = 'Comment' and site.id = comment.site_id and comment.story_id = story.id and story.status in ('public', 'shared', 'open') and story.comment_mode in ('open') and comment.creator_id = creator.id and comment.modifier_id = modifier.id and creator.status <> 'deleted' and modifier.status <> 'deleted' and comment.prototype = metadata.parent_type and comment.id = metadata.parent_id and metadata.name in ('title', 'text') and lower(metadata.value) like lower(?) group by comment.id, comment.created order by comment.created desc limit 51', parameters : [23,'%foobar%']

(Note the prefix, the quotes and parameters at the end.)

Thus, I now introduced a new method to Helma’s DatabaseObject to allow retrieval using a prepared statement and getting the result in the desired structure, too.

The easy route actually was a dead-end: casting the `PreparedStatement` object as a string does not reliably return the desired result (i.e. the actual SQL query). This is due to different DB drivers behaving differently (some compiling the SQL beforehand, some others don’t) and in case of MariaDB we get something like this: ```sql sql : 'select comment.id from content as comment, content as story, site, metadata, account as creator, account as modifier where site.id = ? and comment.prototype = 'Comment' and site.id = comment.site_id and comment.story_id = story.id and story.status in ('public', 'shared', 'open') and story.comment_mode in ('open') and comment.creator_id = creator.id and comment.modifier_id = modifier.id and creator.status <> 'deleted' and modifier.status <> 'deleted' and comment.prototype = metadata.parent_type and comment.id = metadata.parent_id and metadata.name in ('title', 'text') and lower(metadata.value) like lower(?) group by comment.id, comment.created order by comment.created desc limit 51', parameters : [23,'%foobar%'] ``` (Note the prefix, the quotes and `parameters` at the end.) Thus, I now introduced a [new method to Helma’s `DatabaseObject`](https://github.com/antville/helma/pull/55) to allow retrieval using a prepared statement and getting the result in the desired structure, too.