Re: [sqlite] if possible point another table

[Simon Slavin]

On 4 Apr 2014, at 7:55am, Darren Duncan  wrote:

> Putting that aside, for any SQL DBMS that supports the PREPARE and EXECUTE 
> keywords, you can have a SQL string value that contains a SQL statement and 
> execute it, and you can build that string in other SQL from your table like 
> with any string manipulation.  This is a standard way to do it, if not the 
> most elegant, it is simple and powerful.

And represents a huge vulnerability if hackers can trick the application into 
executing their own string.

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

Re: [sqlite] if possible point another table

[Darren Duncan]

On 2014-04-03, 7:19 PM, Andy Goth wrote:

I don't believe this can be done in pure SQL since table names are not
values.  That doesn't mean it can't be done, though you will have to put
some of the logic in your program itself.


I expect that in the future this limitation will no longer exist.  There's no 
reason that table names can't be values in principle. -- Darren Duncan



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

Re: [sqlite] if possible point another table

[mm.w]

refactoring the model using views might be an option too.


On Thu, Apr 3, 2014 at 9:50 PM, mm.w <0xcafef...@gmail.com> wrote:

> Hello,
>
> yep; else; it would require an "eval" or the support to sql scripting, as
> none of this exists, it has to be done at the program level; in a regular
> two queries run, nothing fancy or extraordinary.
>
> Best.
>
>
> On Thu, Apr 3, 2014 at 9:13 PM, Andy Goth  wrote:
>
>> On 4/3/2014 10:10 PM, Keith Medcalf wrote:
>>
>>>
>>>   select *
>>> from k1
>>> union
>>>   select *
>>> from k2;
>>>
>>
>> My understanding of the question was, how to select from tables whose
>> names are somehow computed or extracted from another table
>>
>> --
>> Andy Goth | 
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] if possible point another table

[mm.w]

Hello,

yep; else; it would require an "eval" or the support to sql scripting, as
none of this exists, it has to be done at the program level; in a regular
two queries run, nothing fancy or extraordinary.

Best.


On Thu, Apr 3, 2014 at 9:13 PM, Andy Goth  wrote:

> On 4/3/2014 10:10 PM, Keith Medcalf wrote:
>
>>
>>   select *
>> from k1
>> union
>>   select *
>> from k2;
>>
>
> My understanding of the question was, how to select from tables whose
> names are somehow computed or extracted from another table
>
> --
> Andy Goth | 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] if possible point another table

[Andy Goth]

On 4/3/2014 10:10 PM, Keith Medcalf wrote:


  select *
from k1
union
  select *
from k2;


My understanding of the question was, how to select from tables whose 
names are somehow computed or extracted from another table


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

Re: [sqlite] if possible point another table

[Andy Goth]

On 4/3/2014 8:46 PM, YAN HONG YE wrote:

I have a table named aa like this:
id  pid namenotetablename   
1   0   s12 bbc k1
2   1   sss vac k2

another table named k1:
id  pid namenote
11  1   f2  aaa
12  1   fs  bbc

another table named k2:
id  pid namenote
31  2   f2  aaa
32  2   fs  bbc

my question is: in table aa, if possible k1 point to another table "k1"?

select tablename from aa;
I want result is:
id  pid namenote
11  1   f2  aaa
12  1   fs  bbc
31  2   f2  aaa
32  2   fs  bbc


I don't believe this can be done in pure SQL since table names are not
values.  That doesn't mean it can't be done, though you will have to put
some of the logic in your program itself.  Here's a Tcl implementation
that gives the same result as in your question.  The actual work is done
in the last two lines; the rest is for displaying the result nicely and
for creating the database.

# Recent version of Tcl required for [lmap] and max().
package require Tcl 8.6
package require sqlite3

# Executes an SQL query and prints it in nicely formatted columns.
proc query {db query} {
$db eval $query out {
if {![info exists widths]} {
lappend grid $out(*)
set widths [lmap column $out(*) {string length $column}]
}
lappend grid [set row [lmap column $out(*) {set out($column)}]]
set widths [lmap width $widths value $row {expr {
max($width, [string length $value])
}}]
}
set format %-[join $widths "s %-"]s
set display {}
foreach row $grid {
lappend display [format $format {*}$row]
}
join $display \n
}

# Create the database.
sqlite3 db :memory:
db eval {
CREATE TABLE aa (id, pid, name, note, tablename);
INSERT INTO aa VALUES (1, 0, 's12', 'bbc', 'k1'),
  (2, 1, 'sss', 'vac', 'k2');
CREATE TABLE k1 (id, pid, name, note);
INSERT INTO k1 VALUES (11, 1, 'f2', 'aaa'),
  (12, 1, 'fs', 'bbc');
CREATE TABLE k2 (id, pid, name, note);
INSERT INTO k2 VALUES (31, 1, 'f2', 'aaa'),
  (32, 1, 'fs', 'bbc');
}

# Get list of subqueries which need to be UNION'ed.
set queries [db eval {SELECT 'SELECT * FROM ' || tablename FROM aa}]

# Join subqueries with UNION ALL, then execute and display result.
puts [query db [join $queries " UNION ALL "]]

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