Prev: teapot and libraries
Next: Parsed C++ source code with regsub does not compile on Windows
From: tom.rmadilo on 17 Jun 2010 22:57
On Jun 17, 4:41 pm, "Donal K. Fellows"
<donal.k.fell...(a)manchester.ac.uk> wrote:
> On 17 June, 21:00, lowclouds <x...(a)nowhere.net> wrote:
>
> > I'd like to do something like the following:
>
> >   select * from {select ....} into #temp
> >   go
> >   select x, y, z from #temp where <filter1>
> >   go
> >   select x,y,z from #temp where <filter2>
> >   go
> >
> My thoughts here are that either you should rewrite that so that it is
> a single query (with subqueries, though my SQL skills aren't good
> enough to write an example for you) or you should use a stored
> procedure.

A massive SQL injection attack was just identified this week. IIS and
MS SQL were always involved. One reason is that MS SQL allows multiple
statements including declaring new variables.

Why aid anyone by adding such an unnecessary feature to any database
API? MS explains that SQL injection isn't their fault, they do this on
a page which explains how to exploit buffer overflows to bypass input
string quoting. In other words: their system is designed to fail
unless you hire a few experts to make sure everything is validated at
every API interface.

If the Tcl database API reject multiple statements, celebrate.
From: jr4412 on 17 Jun 2010 23:36
On Jun 18, 3:57 am, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:
> MS explains that SQL injection isn't their fault..

nothing ever is, in fact, the world is at fault for not doing things
the Redmond way. ;(
From: tomk on 18 Jun 2010 13:40
I'm not really familiar with MS SQL but I have used MYSQL and other
database that conform more closely to what is accepted SQL. Temporary
tables should be created using an SQL statement that looks something
like this.
CREATE LOCAL TEMPORARY TABLE ....
The folk at MS choose to go their own way which means you're stuck
with trying to do something that isn't very compatible with the rest
of the world.
tomk

On Jun 17, 1:00 pm, lowclouds <x...(a)nowhere.net> wrote:
> Does one exist for tcl that will talk to MS SQL Server?
>
> I'd like to do something like the following:
>
> select * from {select ....} into #temp
> go
> select x, y, z from #temp where <filter1>
> go
> select x,y,z from #temp where <filter2>
> go
> ....
>
> tclodbc doesn't complain when I try this, but also doesn't work: it only
> returns the result of the first select from the temp table (in this
> case, the go statements are replaced by semi-colons.)
>
> tdbc::odbc explicitly rejects semi-colons and go's are rejected by the
> server.
>
> It appears that 'db connect' in all it's incarnations is not really what
> the server thinks of as a connection, because the temp table disappears
> between calls to 'db $sql' or 'db allrows $sql' or all the other
> variants i've tried.
>
> is there some way of doing this that actually works or am I out of luck.
>
> for anyone familiar with MS SQL Server Management Studio, one can create
> a new 'query' and do the following
>
> select getdate() as adate into #temp;
> select * from #temp;
> select adate as bdate from #temp
>
> and get two results sets. the temp table persists across statement
> boundaries. This is what I can't figure out how to do with tclodbc or
> tdbc::odbc
>
> craig

From: tom.rmadilo on 18 Jun 2010 14:53
On Jun 18, 10:40 am, tomk <krehbiel....(a)gmail.com> wrote:
> I'm not really familiar with MS SQL but I have used MYSQL and other
> database that conform more closely to what is accepted SQL. Temporary
> tables should be created using an SQL statement that looks something
> like this.
>    CREATE LOCAL TEMPORARY TABLE ....
> The folk at MS choose to go their own way which means you're stuck
> with trying to do something that isn't very compatible with the rest
> of the world.
> tomk
>
> On Jun 17, 1:00 pm, lowclouds <x...(a)nowhere.net> wrote:
>
>
>
> > Does one exist for tcl that will talk to MS SQL Server?
>
> > I'd like to do something like the following:
>
> > select * from {select ....} into #temp
> > go
> > select x, y, z from #temp where <filter1>
> > go
> > select x,y,z from #temp where <filter2>
> > go
> > ....
>
> > tclodbc doesn't complain when I try this, but also doesn't work: it only
> > returns the result of the first select from the temp table (in this
> > case, the go statements are replaced by semi-colons.)
>
> > tdbc::odbc explicitly rejects semi-colons and go's are rejected by the
> > server.
>
> > It appears that 'db connect' in all it's incarnations is not really what
> > the server thinks of as a connection, because the temp table disappears
> > between calls to 'db $sql' or 'db allrows $sql' or all the other
> > variants i've tried.
>
> > is there some way of doing this that actually works or am I out of luck..
>
> > for anyone familiar with MS SQL Server Management Studio, one can create
> > a new 'query' and do the following
>
> > select getdate() as adate into #temp;
> > select * from #temp;
> > select adate as bdate from #temp
>
> > and get two results sets. the temp table persists across statement
> > boundaries. This is what I can't figure out how to do with tclodbc or
> > tdbc::odbc

The whole concept of SQL injection is the inability to distinguish
between application supplied code and user supplied data. MS SQL just
makes it much harder to distinguish these two things.

If a user can execute DDL or define variables, then you are allowing
the user to write code. If you allow users to overflow buffers and
silently truncate an SQL statement, you are allowing users to write
code.

Compare:

update x set a = b where c = 'd';

and

update x set a = b;

SQL server allows users to declare fixed length variables then if the
input is too long, the result is truncated.

How friggin' stupid is that?

Combine these features and you have a fertile environment for SQL
injection.

Technically SQL injection should be defeated by being able to
distinguish between user supplied data and sql code, and quoting the
user supplied data. MS SQL server allows several additional vectors of
attack.
From: lowclouds on 18 Jun 2010 19:49
Replying to my original question...

The problem, as I saw it, was that I wanted to make multiple queries
against a temp table and did not see how to do this using either tclodbc
or tdbc::odbc. It appeared that the lifetime of a temp table was the
batch of sql statements submitted to the MS SQL Server and I ran into
the problem that either batches of sql statements were explicitly not
supported, tdbc::odbc, or possibly, that multiple result sets were not
supported, tclodbc.

It turns out that MS SQL Server supports two kinds of temp tables, local
and global, and that global temp tables do survive sql statement calls.
local tables are called #tmp and global tables, ##mytemp.
So, now, I can happily continue to use tclodbc or tdbc::odbc.

Thanks for all the pertinent replies,
Craig

Note: I don't see a consistent syntax for creating temporary tables in
use. MS doesn't seem to support 'create temporary table', but 'create
local temporary table' also doesn't appear to be standard.
It also doesn't seem like sql batches are disallowed by ODBC, but I can
see how supporting them could be difficult.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: teapot and libraries
Next: Parsed C++ source code with regsub does not compile on Windows