Quantcast

BUG: CLI generates invalid SQL for strings with many newlines

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

BUG: CLI generates invalid SQL for strings with many newlines

Clemens Ladisch
Hi,

since commit 68f6dc7af1013f29, newlines in the .dump output are escaped
with char().  This can make the resulting SQL too complex:

$ sqlite3 test.db "create table t(x); insert into t values(replace(printf('%1000s', ''), ' ', char(10)));"
$ sqlite3 test.db .dump | sqlite3
Error: near line 4: Expression tree is too large (maximum depth 1000)

(originally reported here: http://stackoverflow.com/q/43145117/11654)


Regards,
Clemens
_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

James K. Lowden
On Fri, 31 Mar 2017 21:59:05 +0200
Clemens Ladisch <[hidden email]> wrote:

> since commit 68f6dc7af1013f29, newlines in the .dump output are
> escaped with char().  

Why not use vis(3) instead?  No one seemed to notice when I offered
the suggestion.  

Newline encoding is a solved problem.  There's no need to touch the
SQL and introduce nonstandard syntax to the dump format.  

--jkl

_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

Clemens Ladisch
James K. Lowden wrote:
> Clemens Ladisch <[hidden email]> wrote:
>
>> since commit 68f6dc7af1013f29, newlines in the .dump output are
>> escaped with char().
>
> Why not use vis(3) instead?

Because vis() is a nonstandard function that is not available everywhere,
and in any case it does not support SQL.


Regards,
Clemens
_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

James K. Lowden
On Sun, 2 Apr 2017 09:40:36 +0200
Clemens Ladisch <[hidden email]> wrote:

> > Why not use vis(3) instead?
>
> Because vis() is a nonstandard function that is not available
> everywhere,

"everywhere" is a high standard, but vis is freely available and
included or packaged with almost anything not Windows.  By using the
nonstandard char() function, you introduce a constraint that is
otherwise avoided.  

        $ for S in c h m o
        do
                printf '%s: %s\n' -$S $(echo hello | vis -w -$S)
        done
        -c: hello\n
        -h: hello%0a
        -m: hello\012
        -o: hello\012


For the intended purpose, it would be trivial to support some
form of VIS_WHITE.  That could be a fallback position.  

Because the library is widely available, SQLite users who can take
advantage of it can decode the vis-encoded text outside the aegis of
SQLite.  

> and in any case it does not support SQL.

Entirely irrelevant, as you surely know.  

To me, the most objectionable aspect of using char() is that the SQL is
munged.  I see no reason to modify the user's text and introduce
further SQL interpretation.

--jkl
_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

Charles Leifer
This bit me... I fat-fingered a command and deleted my database. I had a
backup dump taken earlier in the day. Go to restore it and all of a sudden
this error starts cropping up.

I'm stuck. I need to get this database back online, but even with 4000
limit I am losing INSERTs. I get segfaults when I disable the limit
completely.

What to do?

On Mon, Apr 3, 2017 at 7:16 PM, James K. Lowden <[hidden email]>
wrote:

> On Sun, 2 Apr 2017 09:40:36 +0200
> Clemens Ladisch <[hidden email]> wrote:
>
> > > Why not use vis(3) instead?
> >
> > Because vis() is a nonstandard function that is not available
> > everywhere,
>
> "everywhere" is a high standard, but vis is freely available and
> included or packaged with almost anything not Windows.  By using the
> nonstandard char() function, you introduce a constraint that is
> otherwise avoided.
>
>         $ for S in c h m o
>         do
>                 printf '%s: %s\n' -$S $(echo hello | vis -w -$S)
>         done
>         -c: hello\n
>         -h: hello%0a
>         -m: hello\012
>         -o: hello\012
>
>
> For the intended purpose, it would be trivial to support some
> form of VIS_WHITE.  That could be a fallback position.
>
> Because the library is widely available, SQLite users who can take
> advantage of it can decode the vis-encoded text outside the aegis of
> SQLite.
>
> > and in any case it does not support SQL.
>
> Entirely irrelevant, as you surely know.
>
> To me, the most objectionable aspect of using char() is that the SQL is
> munged.  I see no reason to modify the user's text and introduce
> further SQL interpretation.
>
> --jkl
> _______________________________________________
> 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: BUG: CLI generates invalid SQL for strings with many newlines

Clemens Ladisch
Charles Leifer wrote:
> This bit me... I fat-fingered a command and deleted my database. I had a
> backup dump taken earlier in the day. Go to restore it and all of a sudden
> this error starts cropping up.
>
> What to do?

From the link the in the first post:

  sed -e "s/'||char(10)||'/\\n/g" < with_char.sql > with_newlines.sql

(Same for char(13) → \r, if you have MS-DOS or Mac line endings.)


Regards,
Clemens
_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

Clemens Ladisch
In reply to this post by James K. Lowden
James K. Lowden wrote:
>>> Why not use vis(3) instead?
>>
>> Because vis() is a nonstandard function that is not available
>> everywhere,
>
> "everywhere" is a high standard, but vis is freely available and
> included or packaged with almost anything not Windows.

It is not included in the distribution that I happen to use.

>> and in any case it does not support SQL.
>
> Entirely irrelevant, as you surely know.

What?!?  The .dump output _is_ SQL, and is intended to be read again
by SQLite, so whatever escaping mechanism it uses must be supported by
SQLite itself.

> To me, the most objectionable aspect of using char() is that the SQL is
> munged.  I see no reason to modify the user's text and introduce
> further SQL interpretation.

The INSERT statements _already_ are interpreted; that's the whole point
of generating them in the first place.


Regards,
Clemens
_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

Charles Leifer
I hate to be critical of Dr Hipp, but this commit stinks. Even if using the
sqlite3 shell isn't the blessed way of producing a backup, I'm sure a lot
of folks prefer it to the online backup API. It's this simple:

"echo .dump | sqlite3 my_db.db | gzip > my_db.sql.gz"

That's beautiful.

And now it doesn't work.

What a horrible shock it was to discover that my backups were all of a
sudden failing. I had no idea why until I checked the mailing list, which I
luckily happen to be subscribed to, otherwise I'd still be puzzling over
what happened.

Please consider reverting this, or hiding this behavior behind a flag.

On Tue, Apr 4, 2017 at 1:55 AM, Clemens Ladisch <[hidden email]> wrote:

> James K. Lowden wrote:
> >>> Why not use vis(3) instead?
> >>
> >> Because vis() is a nonstandard function that is not available
> >> everywhere,
> >
> > "everywhere" is a high standard, but vis is freely available and
> > included or packaged with almost anything not Windows.
>
> It is not included in the distribution that I happen to use.
>
> >> and in any case it does not support SQL.
> >
> > Entirely irrelevant, as you surely know.
>
> What?!?  The .dump output _is_ SQL, and is intended to be read again
> by SQLite, so whatever escaping mechanism it uses must be supported by
> SQLite itself.
>
> > To me, the most objectionable aspect of using char() is that the SQL is
> > munged.  I see no reason to modify the user's text and introduce
> > further SQL interpretation.
>
> The INSERT statements _already_ are interpreted; that's the whole point
> of generating them in the first place.
>
>
> Regards,
> Clemens
> _______________________________________________
> 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: BUG: CLI generates invalid SQL for strings with many newlines

Charles Leifer
Is it conceivable that this change could be rolled back?

On Tue, Apr 4, 2017 at 9:45 AM, Charles Leifer <[hidden email]> wrote:

> I hate to be critical of Dr Hipp, but this commit stinks. Even if using
> the sqlite3 shell isn't the blessed way of producing a backup, I'm sure a
> lot of folks prefer it to the online backup API. It's this simple:
>
> "echo .dump | sqlite3 my_db.db | gzip > my_db.sql.gz"
>
> That's beautiful.
>
> And now it doesn't work.
>
> What a horrible shock it was to discover that my backups were all of a
> sudden failing. I had no idea why until I checked the mailing list, which I
> luckily happen to be subscribed to, otherwise I'd still be puzzling over
> what happened.
>
> Please consider reverting this, or hiding this behavior behind a flag.
>
> On Tue, Apr 4, 2017 at 1:55 AM, Clemens Ladisch <[hidden email]>
> wrote:
>
>> James K. Lowden wrote:
>> >>> Why not use vis(3) instead?
>> >>
>> >> Because vis() is a nonstandard function that is not available
>> >> everywhere,
>> >
>> > "everywhere" is a high standard, but vis is freely available and
>> > included or packaged with almost anything not Windows.
>>
>> It is not included in the distribution that I happen to use.
>>
>> >> and in any case it does not support SQL.
>> >
>> > Entirely irrelevant, as you surely know.
>>
>> What?!?  The .dump output _is_ SQL, and is intended to be read again
>> by SQLite, so whatever escaping mechanism it uses must be supported by
>> SQLite itself.
>>
>> > To me, the most objectionable aspect of using char() is that the SQL is
>> > munged.  I see no reason to modify the user's text and introduce
>> > further SQL interpretation.
>>
>> The INSERT statements _already_ are interpreted; that's the whole point
>> of generating them in the first place.
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> 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: BUG: CLI generates invalid SQL for strings with many newlines

Dominique Devienne
On Fri, Apr 7, 2017 at 5:02 AM, Charles Leifer <[hidden email]> wrote:

> Is it conceivable that this change could be rolled back?
>
> On Tue, Apr 4, 2017 at 9:45 AM, Charles Leifer <[hidden email]> wrote:
> > I hate to be critical of Dr Hipp, but this commit stinks. Even if using
> > the sqlite3 shell isn't the blessed way of producing a backup, I'm sure a
> > lot of folks prefer it to the online backup API. It's this simple:
>

I reached to colleagues about this, to find out if they depend on .dump in
our
commercial server product heavily using SQLite, and indeed they do. In fact
for many admin tasks, their rule is "use only the official shell via
scripts" and
no C code, so they also consider this change a "regression" I'm afraid.
FWIW. --DD

PS: Just at the time we're upgrading all our 3rd parties to gear up for our
next release
_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

Warren Young
On Apr 7, 2017, at 1:14 AM, Dominique Devienne <[hidden email]> wrote:
>
> On Fri, Apr 7, 2017 at 5:02 AM, Charles Leifer <[hidden email]> wrote:
>
>> Is it conceivable that this change could be rolled back?
>
> ...their rule is "use only the official shell via
> scripts” and no C code

As one who just ported a shell script written as a wrapper around the sqlite3 executable to Perl + DBI because it had grown beyond the natural bounds of shell scripting, I have to ask “WHY???”

Don’t misunderstand, it’s great that you have the option to write simpler scripts as sqlite3 shell script wrappers, but at some point, there really are benefits to moving to something more powerful.

(In my case, it was largely to avoid paying the cost of calling sqlite3 thousands of times in a loop with near-identical SQL, whereas the Perl script keeps the conn open and calls prepare() a few times to avoid repeated SQL re-parsing.  The Perl version probably runs a couple of orders of magnitude faster.)

I also want to know how a systems administration organization centered around Unix shell scripting gets to 2017 without any awareness that command output formats occasionally change?  That’s a perennial danger.

I can get behind the “no C” rule, since that just buys a different pile of portability problems.  You need a really compelling reason to write something in C.  (SQLite itself has one of those good reasons.)  But what’s wrong with Perl, or Python, or Ruby, or Tcl, or… ?  They all have stable SQLite DB access libraries, often included with the base OS, since so many things depend on those scripting languages *and* SQLite.

I haven’t moved all of my sqlite3 shell script wrappers to Perl + DBI, and I won’t be doing so.  Sometimes that’s exactly the right way to go.  My objection is to this apparent “only one right way to do it” mentality.  C, shell, and your favorite scripting language are all the “right way” in some contexts.
_______________________________________________
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: BUG: CLI generates invalid SQL for strings with many newlines

Warren Young
On Apr 7, 2017, at 10:45 AM, Warren Young <[hidden email]> wrote:
>
> (In my case, it was largely to avoid paying the cost of calling sqlite3 thousands of times in a loop with near-identical SQL

I just remembered the other reason: because it’s easier to manage SQL quoting rules in terms of Perl quoting rules than in terms of shell quoting.

Talk about bringing it around full-circle.

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