Re: [sqlite] Escape table and column names

[Jean-Christophe Deschamps]

At 13:55 16/05/2012, you wrote:
´¯¯¯

Those all work. You are getting an error because you didn't supply any
column in the table

`---

Oops, that's what happens when you try doing too many things at the 
same time.


Nonetheless,

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table [foo [[bar]] ?] (a);
Error: unrecognized token: "]"

It would be logical to have ]] represent a single ] (standard escape by 
doubling), while [[ would be left as [[ since there's ne need to escape 
the opening bracket [.


But yes, this one works:

sqlite> create table `foo ``bar`` ?` (a);
sqlite> 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Jay A. Kreibich]

On Wed, May 16, 2012 at 01:47:01PM +0200, Jean-Christophe Deschamps scratched 
on the wall:
> 
> >> This FAQ topic doesn't apply here.  We're talking about schema
> >names or keywords, while FAQ#14 is literal escaping a single
> >quote.
> >
> >create table "foo""bar" ( baz );
> >
> >select * from "one""two";
> >
> >select * from sqlite_master;
> >
> >table|foo"bar|foo"bar|9|CREATE TABLE "foo""bar" ( baz )

> I knew this particular escape worked, granted, but this isn't documented.

  All three of these are part of the SQL standard:

  A) The use of single quotes to define string literals
  B) The use of double quotes to define identifiers**.
  C) The use of repeat characters as an escape.

  ** An "identifer" in SQL is an object name, such as a database name,
 table name, or column name.

> So some questions remain: what are the definitive rules?

  Unless otherwise documented, the SQL standard.  See above three points.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Baruch Burstein]

On Wed, May 16, 2012 at 2:47 PM, Jean-Christophe Deschamps  wrote:

>
>  > This FAQ topic doesn't apply here.  We're talking about schema names or
>> keywords, while FAQ#14 is literal escaping a single quote.
>>
>> create table "foo""bar" ( baz );
>>
>> select * from "one""two";
>>
>> select * from sqlite_master;
>>
>> table|foo"bar|foo"bar|9|CREATE TABLE "foo""bar" ( baz )
>>
>
> I knew this particular escape worked, granted, but this isn't documented.
>
> But even then:
>
> SQLite version 3.7.11 2012-03-20 11:35:50
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table [foo [[bar]] ?];
> Error: unrecognized token: "]"
> sqlite> create table `foo ``bar`` ?`;
> Error: near ";": syntax error
> sqlite> create table `foo ``bar ?`;
> Error: near ";": syntax error
>
> So some questions remain: what are the definitive rules?
>

Those all work. You are getting an error because you didn't supply any
column in the table



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Jean-Christophe Deschamps]

> This FAQ topic doesn't apply here.  We're talking about schema 
names or keywords, while FAQ#14 is literal escaping a single quote.


create table "foo""bar" ( baz );

select * from "one""two";

select * from sqlite_master;

table|foo"bar|foo"bar|9|CREATE TABLE "foo""bar" ( baz )


I knew this particular escape worked, granted, but this isn't documented.

But even then:

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table [foo [[bar]] ?];
Error: unrecognized token: "]"
sqlite> create table `foo ``bar`` ?`;
Error: near ";": syntax error
sqlite> create table `foo ``bar ?`;
Error: near ";": syntax error

So some questions remain: what are the definitive rules?

JcD 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Petite Abeille]

On May 16, 2012, at 1:15 PM, Jean-Christophe Deschamps wrote:

> This FAQ topic doesn't apply here.  We're talking about schema names or 
> keywords, while FAQ#14 is literal escaping a single quote.

create table "foo""bar" ( baz );

select * from "one""two";

select * from sqlite_master;

table|foo"bar|foo"bar|9|CREATE TABLE "foo""bar" ( baz )
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Baruch Burstein]

On Wed, May 16, 2012 at 2:25 PM, Simon Slavin  wrote:

>
> On 16 May 2012, at 11:56am, Petite Abeille 
> wrote:
>
> > On May 16, 2012, at 12:28 PM,  
> wrote:
> >
> >> Anyone have an answer?
> >
> > http://www.sqlite.org/lang_keywords.html
> >
> >> What about if table name or column name contains that escape character?
> >> Do I need to escape it in another way?
> >
> > http://sqlite.org/faq.html#q14
>
> Doesn't answer the question of what to do if your table name includes a
> double-quote character.
>
> And nor can I, except to say that if I saw such a thing I'd run away.
>
> A quick testing shows it works the same for double-quoted column names,
e.g.

sqlite> create table "q""w" (t);
sqlite> .tables
q"w

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Simon Slavin]

On 16 May 2012, at 11:56am, Petite Abeille  wrote:

> On May 16, 2012, at 12:28 PM,   wrote:
> 
>> Anyone have an answer?
> 
> http://www.sqlite.org/lang_keywords.html
> 
>> What about if table name or column name contains that escape character?
>> Do I need to escape it in another way?
> 
> http://sqlite.org/faq.html#q14

Doesn't answer the question of what to do if your table name includes a 
double-quote character.

And nor can I, except to say that if I saw such a thing I'd run away.

SImon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Jean-Christophe Deschamps]

> What about if table name or column name contains that escape character?
> Do I need to escape it in another way?

http://sqlite.org/faq.html#q14


This FAQ topic doesn't apply here.  We're talking about schema names or 
keywords, while FAQ#14 is literal escaping a single quote.


Given that we have 3 ways to quote a schema name ("abc def", [abc def] 
and `abc def`) it's unlikely that a user would select a real-world name 
including all 3 " [ ` as significant characters.  So that "solves" the 
issue for user entried schema names.


OTOH I agree with Marco in that it would be nice to have a solid rule 
that could be implemented in software to properly quote and escape 
schema names in the general case.


JcD

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Petite Abeille]

On May 16, 2012, at 12:28 PM,   wrote:

> Anyone have an answer?

http://www.sqlite.org/lang_keywords.html

> What about if table name or column name contains that escape character?
> Do I need to escape it in another way?

http://sqlite.org/faq.html#q14

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users