Convert complete result set into String within SQL (HSQLDB)

Question

As part of a Secure Code course I am given a vulnerable Java system, where queries are built up using string concatintion, and so on. Following is the respective code line that I have to SQL inject to retrieve log-in data:

String comment;
int articleID;
//...
stmt.executeUpdate("INSERT INTO comment (text, article_id) VALUES ('" + comment + "', " + articleId + ")");

comment can be set to any text, it is just read plain-text from a textbox. Therefore, I thought about setting the textbox text to an SELECT statement, reading the complete login data (table user has columns login and password):

' || (SELECT login || password FROM user) || '

which in overall should result in the query

INSERT INTO comment (text, article_id) VALUES ('' || (SELECT login || password FROM user) || '', 4)

yet, this results in

org.hsqldb.HsqlException: cardinality violation

Question:

I assume this is because (SELECT login || password FROM user) is not a single string, but a result set, and may therefore not be concatenated using ||.

Is it possible to convert the complete result set to one string in a way it can be used in this SQL injection scenario (in standard SQL / SQL that works on HSQLDB)?

fredt · Accepted Answer · 2016-03-26 19:41:40Z

2

You are correct, as there is obviously more than one user and multiple rows are returned from the SELECT.

You can add LIMIT 1 to the SELECT to get the first row. You can also create a string array from the inner SELECT. See the guide.

In HSQLDB each database user has separate access rights to the tables. In a real deployment the user that inserts comments will not even see the existence of the table that contains the password, let alone select from it.

answered Mar 26, 2016 at 19:41

fredt

24.5k3 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert complete result set into String within SQL (HSQLDB)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related