Quantcast

Can stmt SQL be changed?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Can stmt SQL be changed?

curmudgeon
For a while I got away with this

        sqlite3_stmt *stmt;

        if (sqlite3_prepare16_v2(DB, L”sql statement”, -1, &stmt, NULL) != SQLITE_OK)
                throw exception(“.....”);

        // use stmt

        sqlite3_reset(stmt);


        if (sqlite3_prepare16_v2(DB, L”different sql statement”, -1, &stmt, NULL) != SQLITE_OK)
                throw exception(“.....”);


        // use stmt again

        sqlite3_finalize(stmt);

(I’m using a c++ SQLite wrapper I wrote myself so hopefully I’ve written the above out correctly).

Anyway, on the latest attempt at using the above everything worked fine as before. No error messages, stmt executed fine before and after SQL change and was finalized without getting an errmsg. This time though I got an error message when I tried to close the DB (something along the lines of “unable to close db ... unfinalized stmts or ...”.

The reused stmt was the only possible culprit so I’m wondering if it’s down to the changed sql?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

Simon Slavin-3

On 2 Feb 2017, at 9:28am, x <[hidden email]> wrote:

> The reused stmt was the only possible culprit so I’m wondering if it’s down to the changed sql?

You can find out.  Both _reset() and _finalize() return a result code just like _prepare().  Do the same kind of exception checking for them, just in case they’re return an error.  It won’t tell you definitely what’s wrong but it might help.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

curmudgeon
Thanks for the  reply Simon but the wrapper throws an exception if either reset or finalize fails to return SQLITE_OK so not that.



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10



From: Simon Slavin<mailto:[hidden email]>
Sent: 02 February 2017 10:41
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] Can stmt SQL be changed?



On 2 Feb 2017, at 9:28am, x <[hidden email]> wrote:

> The reused stmt was the only possible culprit so I’m wondering if it’s down to the changed sql?

You can find out.  Both _reset() and _finalize() return a result code just like _prepare().  Do the same kind of exception checking for them, just in case they’re return an error.  It won’t tell you definitely what’s wrong but it might help.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

Simon Slavin-3

On 2 Feb 2017, at 11:08am, x <[hidden email]> wrote:

> Thanks for the  reply Simon but the wrapper throws an exception if either reset or finalize fails to return SQLITE_OK so not that.

Okay.  So your question boils down to whether the second _prepare() in this is legal:

_open()
  _prepare()
    _step()
  _reset()
  _prepare()
    _step()
  _reset()
_close()

and you’re getting an error returned on _close() rather than on the _prepare() itself.

The documentation doesn’t seem to say whether it’s legal or not, but I feel that if it isn’t, you should be getting SQLITE_MISUSE returned from the second _prepare().

You appear to be doing appropriate error-trapping.  I’m out of ideas but I hope with that restatement of your problem someone else can help you.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

Richard Hipp-3
In reply to this post by curmudgeon
On 2/2/17, x <[hidden email]> wrote:

> For a while I got away with this
>
>         sqlite3_stmt *stmt;
>
>         if (sqlite3_prepare16_v2(DB, L”sql statement”, -1, &stmt, NULL) !=
> SQLITE_OK)
>                 throw exception(“.....”);
>
>         // use stmt
>
>         sqlite3_reset(stmt);
>
>
>         if (sqlite3_prepare16_v2(DB, L”different sql statement”, -1, &stmt,
> NULL) != SQLITE_OK)
>                 throw exception(“.....”);

This second prepare overwrites the "stmt" pointer with a pointer to a
new object.  The old statement was never finalized.  This results in a
statement object leak, which SQLite detects when you try to close the
database connection.


>
>
>         // use stmt again
>
>         sqlite3_finalize(stmt);
>
> (I’m using a c++ SQLite wrapper I wrote myself so hopefully I’ve written the
> above out correctly).
>
> Anyway, on the latest attempt at using the above everything worked fine as
> before. No error messages, stmt executed fine before and after SQL change
> and was finalized without getting an errmsg. This time though I got an error
> message when I tried to close the DB (something along the lines of “unable
> to close db ... unfinalized stmts or ...”.
>
> The reused stmt was the only possible culprit so I’m wondering if it’s down
> to the changed sql?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

Igor Tandetnik-2
In reply to this post by curmudgeon
On 2/2/2017 4:28 AM, x wrote:

> For a while I got away with this
>
>         sqlite3_stmt *stmt;
>
>         if (sqlite3_prepare16_v2(DB, L”sql statement”, -1, &stmt, NULL) != SQLITE_OK)
>                 throw exception(“.....”);
>
>         // use stmt
>
>         sqlite3_reset(stmt);
>
>
>         if (sqlite3_prepare16_v2(DB, L”different sql statement”, -1, &stmt, NULL) != SQLITE_OK)
>                 throw exception(“.....”);
>
>
>         // use stmt again
>
>         sqlite3_finalize(stmt);

You are simply leaking a statement. You prepare one, never finalize it,
prepare another, and finalize the latter. The fact that you use the same
variable to store both handles is irrelevant (apart from the fact that
it makes it easier to leak). It's no different than, say,

char* p;
p = new char[42];
p = new char[84];  // previous allocation leaked.
delete[] p;  // second allocation freed

Just finalize the first statement before reusing the variable for the
second one.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

Simon Slavin-3
So would it be possible to detect someone using _prepare() on an existing statement and return SQLITE_MISUSE ?

It would slow things down, of course, so there’s a little trade-off between detecting the error and making _prepare() a tiny bit slower.  But it should be a very very tiny bit.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

Igor Tandetnik-2
On 2/2/2017 8:11 AM, Simon Slavin wrote:
> So would it be possible to detect someone using _prepare() on an existing statement and return SQLITE_MISUSE ?

Well, one could be passing an address of uninitialized variable to
sqlite_prepare. You probably don't want to error out if the random
garbage it contains just accidentally happens to match a valid statement
handle. That kind of heisenbug would be a doozy to debug.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can stmt SQL be changed?

curmudgeon
In reply to this post by Richard Hipp-3
Thanks for the replies. I’ve amended the wrapper to finalize the stmt before any change to sql. Only required a one word change.





From: Richard Hipp<mailto:[hidden email]>
Sent: 02 February 2017 12:27
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] Can stmt SQL be changed?



On 2/2/17, x <[hidden email]> wrote:

> For a while I got away with this
>
>         sqlite3_stmt *stmt;
>
>         if (sqlite3_prepare16_v2(DB, L”sql statement”, -1, &stmt, NULL) !=
> SQLITE_OK)
>                 throw exception(“.....”);
>
>         // use stmt
>
>         sqlite3_reset(stmt);
>
>
>         if (sqlite3_prepare16_v2(DB, L”different sql statement”, -1, &stmt,
> NULL) != SQLITE_OK)
>                 throw exception(“.....”);

This second prepare overwrites the "stmt" pointer with a pointer to a
new object.  The old statement was never finalized.  This results in a
statement object leak, which SQLite detects when you try to close the
database connection.


>
>
>         // use stmt again
>
>         sqlite3_finalize(stmt);
>
> (I’m using a c++ SQLite wrapper I wrote myself so hopefully I’ve written the
> above out correctly).
>
> Anyway, on the latest attempt at using the above everything worked fine as
> before. No error messages, stmt executed fine before and after SQL change
> and was finalized without getting an errmsg. This time though I got an error
> message when I tried to close the DB (something along the lines of “unable
> to close db ... unfinalized stmts or ...”.
>
> The reused stmt was the only possible culprit so I’m wondering if it’s down
> to the changed sql?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...