Pagination with SQLite using LIMIT

I'm writing my own SQLIteBrowser and I have one final problem, which apparently is quite often discussed on the web, but it doesn't seem to have a good general solution.

So currently I store the SQL which the user entered. Whenever I need to fetch rows, I execute the SQL by adding "Limit n, m` at the end of the SQL.

For normal SQLs, which I mostly use, this seems good enough. However if I want to use limit myself in the query, this will obviously give an error, because the resulting sql can look like this:

 select * from table limit 30 limit 1,100

which is obviously wrong. Is there some better way to do this?

My idea was, that I could scan the SQL and check if there is a limit clause already used and then ignore it. Of course it's not as simlpe as that, because if I have an sql like this:

 select * from a where a.b = ( select x from z limit 1)

it obviously should still apply my limit in such a case, so I could scan the string from the end and look if there is a limit somehwere. My question now is, how feasable this is. As I don't know who the SQL parser works, I'm not sure if LIMIT has to be at the end of SQL or if there can be other commands at the end as well.

I tested it with order byand group by and I get SQL errors if limit is not at the end, so my assumption seems to be true.

I found now a much better solution which is quite simple and doesn't require me to parse the SQL.

The user can enter an arbitrary sql. The result is loaded into a table. Since we don't want to load the whole result at once, as this can return millions of records, only N records are retriueved. When the user scroll to the bottom of the table the next N items are fetched and loaded into the table.

The solution is, to wrapt the SQL into an outer sql with my page size limits.

 select * from (arbitrary UserSQL) limit PageSize, CurrentOffset

I tested it with SQLs I regularly use, and this seem to work quite nicely and is also fast enough for my purpose.

However, I don't know wether SQLite has a mechanism to fetch the new rows faster, or if the sql has to be rerun every time. In that case it might not be a good solution fo rrealy complex queries with a long response time.