SQLITE random value/row with criteria

Question

So, i know that the question about getting a random row in SQL database have been posted (even more than one time), and it helped me get to this SQL query:

"SELECT column FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT 1)";

if i understand that well, order by Random is not efficient on large table but if you use it with the id it's fast enough.

So i've tried it, it seems to work well, but then i wanted to add a criteria, so i've changed it just a little bit:

"SELECT column1 FROM table WHERE (column2 LIKE '" + value + "' AND id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT 1)";

To be more clear, i have this table: uploaded = (id, filename, owner). I want to select a random filename from a given owner. Which gives me that:

"SELECT filename FROM uploaded WHERE (owner LIKE '" + owner + "' AND id IN (SELECT id FROM uploaded ORDER BY RANDOM() LIMIT 1))";

Problem is: sometimes, rs.next is false (which mean i don't have a result). However, i print the DB before searching for the filename, so i am SURE that the owner does have a related filename in this DB. Maybe only one, but still one.

This bring me to the question: what's wrong with my query?

One thing I'm sure about *what's wrong with my query?* that is victim of SQL Injection — Youcef LAIDANI, Aug 27 '18 at 12:24
Well, you should only get the random ID for OWNER... here you get a random ID and expect it to also be owned by `owner`. — AxelH, Aug 27 '18 at 12:25

AxelH · Accepted Answer · 2018-08-27T12:32:04.410

4

You want to get a random id for owner, not any ID. That's why you get an empty result sometime. You get an ID that's not for OWNER, so both condition are not TRUE.

So add the condition in this query :

SELECT id FROM uploaded where owner = ? ORDER BY RANDOM() LIMIT 1

No need to check twice for owner, you already got an id for him. So :

SELECT filename FROM uploaded WHERE id IN (
    SELECT id FROM uploaded where owner LIKE ? ORDER BY RANDOM() LIMIT 1
)

PS: this is using a PreparedStatement notation.

PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, owner);
ResultSet rs = stmt.executeQuery();

I suggest you check it to prevent any SQL injection or you can replace ? by your String.

edited Aug 27 '18 at 12:32

answered Aug 27 '18 at 12:27

AxelH

14,325
2
25
55

Thanks (to both of the answers, which are pretty much the same). This works well. And i also wanted to learn how to use this ? with SQL queries, so that makes two answers in one! I will change all my SQL queries with that ? now :) – Ablia Aug 27 '18 at 12:37
Important point about `PreparedStatement` argument, index start at 1 ! @Ablia – AxelH Aug 27 '18 at 12:38
Yeah i know, i was already using it with 'VALUES(?,?)', but i didn't know how to use it with other things. It is pretty much the same, in fact. – Ablia Aug 27 '18 at 13:01

score 2 · Answer 2 · answered Aug 27 '18 at 12:26

The problem is that you select a random row and then check that the column has a particular value. You should do this the other way around by putting the WHERE clause on the inner select:

"SELECT filename " +
"FROM uploaded " +
"WHERE id in " +
"  (SELECT id " +
"   FROM uploaded " +
"   WHERE owner LIKE ? " +
"   ORDER BY RANDOM() LIMIT 1)";

Note that I use ? instead of concatenating the owner directly into the query. You should pass the correct value as a parameter to the correct SQLite function. This is called a bound parameter and will ensure that you protect yourself against SQL injection.

SQLITE random value/row with criteria

2 Answers2