Sql injecting

[ofiras]

Hii everyone,
I'm a web programmer, but I never understood sql injecting.
All I found was that you can write "a' or 'a'='a" in the password
field to try to connect without knowing the password.
I heard that there are many other ways to do sql injecting, and I
never found how.
I know that you can even manage to get data from sql tables using sql
injecting.
How can it be? How can someone do it?
Please help,
Ofir.

[Ed Murphy]

steve wrote:

In t-sql this should be perfectly clear:
DECLARE @X INT
SET @X=5

The variable @X can only take one value at any specific time.
In a relational system a procedure that returns some value at runtime
must behave exactly like @X.

In general, this is false. For instance, you can't do

SET @MyProcedure = (@X, @Y)

However, you seem to merely be advocating that a procedure should
return exactly one value (which may be a table). Upgrading all the
existing procedures that violate this would be a major task, but
allowing it as an option for new procedures would be reasonable
(if it could be done reasonably efficiently).

Add to this the idea of type where each result is a different type

In other words, TABLE (X INT, Y INT) is a different variable type
from TABLE (M VARCHAR(15), N VARCHAR(15))?

This sql sp:
CREATE PROCEDURE SqlTwo
@A INT OUTPUT,
@B INT OUTPUT
AS
SET @A=5
SET @B=10

DECLARE @C INT,@D INT
EXEC SqlTwo @C OUTPUT,@D OUTPUT
SELECT @C
SELECT @D

makes no sense relationally because, again, there are multiple
results. Now there are two scalar types (int) returned instead of sql
'resultsets'. Relationally there is no such thing as more than 1 thing
(think a variable of a type) at a time. Two scalar results are
realized as a 'row' type relationally, ie. 'one' thing.
create operator D4Two():row(A:I nteger,B:Intege r)
begin
result:=row(5 A,10 B);
end;

In this case at runtime D4Two is a variable of type row with 2 scalar
columns.

What if the data you want to return is not multiple scalars, but
rather multiple tables? Upon reflection, I suppose tables could
be nested in this model, i.e. you can return
TABLE(T1 TABLE(X INT, Y INT), T2 TABLE(M VARCHAR(15), N VARCHAR(15)))

This would allow bad developers to commit the common 'a,b,c' 1NF
violation in a whole new way, but then bad developers can screw
things up in any language.

From the relational perspective a table/row/list is a variable that
behaves exactly like a variable in a programming language. Its value
can be assigned to other values just like a t-sql integer variable
can.
It can be compared to other variables (for equality) just like a t-sql
integer variable. It can be passed as an argument to a procedure just
like a t-sql integer variable. For these reasons why MS decided to
call
something a 'table variable' remains a mystery.

But you agree that (1) it has some features of variables, and (2) it
could reasonably be extended to have more features of variables?

Sql distinguishes between user defined functions and procedures. But
sql user defined functions are on the same exact level of procedures
when looked at from the point of view of 'variables'. Neither one
has anything to do with the idea of a relational variable. All this
artificial distinction does is serve to make it harder for users to
understand the relational model :) (Why sql choose to create a user
define function/procedure dichotomy is another topic. But think of
'where' and 'having').

I'd guess these are both for efficiency. They enforce some useful
clarity, too (I also prefer FROM X JOIN Y ON X.Z = Y.Z over
FROM X, Y WHERE X.Z = Y.Z because table joins are a distinct
concept that's worth keeping separate.)

Rather than center on particular synatax or pseudo-syntax I think it
is
the ideas that the relational model is based on that is important.
And what we're talking about here is just a slice of the relational
model. The relational model is not rocket science:) It's actually
quiet straightforward . Ironically it's sql that is out in left field.
The relational model is in line with all current programming
languages.
Unfortuneatly thats never been the case with sql:) This is one of the
reasons I find LINQ so unnecessary. Once you get the idea that a big
part of the relational model is all about the basic concepts of
variables and types I think (I at least hope) that what I've been
trying to explain will make perfect sense:)

A lot of people find SQL pretty straightforward , especially in this
newsgroup. Your choice of (pseudo-)syntax will make a difference to
them. (You might get different responses from a newsgroup focusing
on front-end programming languages, especially if they already
resemble Pascal as D4 seems to do.)

[jhofmeyr]

On Nov 22, 1:34 am, Ed Murphy <emurph...@soca l.rr.comwrote:

steve wrote:

In t-sql this should be perfectly clear:
DECLARE @X INT
SET @X=5

The variable @X can only take one value at any specific time.
In a relational system a procedure that returns some value at runtime
must behave exactly like @X.

In general, this is false. For instance, you can't do

SET @MyProcedure = (@X, @Y)

However, you seem to merely be advocating that a procedure should
return exactly one value (which may be a table). Upgrading all the
existing procedures that violate this would be a major task, but
allowing it as an option for new procedures would be reasonable
(if it could be done reasonably efficiently).

Add to this the idea of type where each result is a different type

In other words, TABLE (X INT, Y INT) is a different variable type
from TABLE (M VARCHAR(15), N VARCHAR(15))?

This sql sp:
CREATE PROCEDURE SqlTwo
@A INT OUTPUT,
@B INT OUTPUT
AS
SET @A=5
SET @B=10

DECLARE @C INT,@D INT
EXEC SqlTwo @C OUTPUT,@D OUTPUT
SELECT @C
SELECT @D

makes no sense relationally because, again, there are multiple
results. Now there are two scalar types (int) returned instead of sql
'resultsets'. Relationally there is no such thing as more than 1 thing
(think a variable of a type) at a time. Two scalar results are
realized as a 'row' type relationally, ie. 'one' thing.
create operator D4Two():row(A:I nteger,B:Intege r)
begin
result:=row(5 A,10 B);
end;

In this case at runtime D4Two is a variable of type row with 2 scalar
columns.

What if the data you want to return is not multiple scalars, but
rather multiple tables? Upon reflection, I suppose tables could
be nested in this model, i.e. you can return
TABLE(T1 TABLE(X INT, Y INT), T2 TABLE(M VARCHAR(15), N VARCHAR(15)))

This would allow bad developers to commit the common 'a,b,c' 1NF
violation in a whole new way, but then bad developers can screw
things up in any language.

From the relational perspective a table/row/list is a variable that
behaves exactly like a variable in a programming language. Its value
can be assigned to other values just like a t-sql integer variable
can.
It can be compared to other variables (for equality) just like a t-sql
integer variable. It can be passed as an argument to a procedure just
like a t-sql integer variable. For these reasons why MS decided to
call
something a 'table variable' remains a mystery.

But you agree that (1) it has some features of variables, and (2) it
could reasonably be extended to have more features of variables?

Sql distinguishes between user defined functions and procedures. But
sql user defined functions are on the same exact level of procedures
when looked at from the point of view of 'variables'. Neither one
has anything to do with the idea of a relational variable. All this
artificial distinction does is serve to make it harder for users to
understand the relational model :) (Why sql choose to create a user
define function/procedure dichotomy is another topic. But think of

'where' and 'having').

I'd guess these are both for efficiency. They enforce some useful
clarity, too (I also prefer FROM X JOIN Y ON X.Z = Y.Z over
FROM X, Y WHERE X.Z = Y.Z because table joins are a distinct
concept that's worth keeping separate.)

Rather than center on particular synatax or pseudo-syntax I think it
is
the ideas that the relational model is based on that is important.
And what we're talking about here is just a slice of the relational
model. The relational model is not rocket science:) It's actually
quiet straightforward . Ironically it's sql that is out in left field.
The relational model is in line with all current programming
languages.
Unfortuneatly thats never been the case with sql:) This is one of the
reasons I find LINQ so unnecessary. Once you get the idea that a big
part of the relational model is all about the basic concepts of
variables and types I think (I at least hope) that what I've been
trying to explain will make perfect sense:)

A lot of people find SQL pretty straightforward , especially in this
newsgroup. Your choice of (pseudo-)syntax will make a difference to
them. (You might get different responses from a newsgroup focusing
on front-end programming languages, especially if they already
resemble Pascal as D4 seems to do.)- Hide quoted text -

- Show quoted text -

I feel like I'm watching a Greek person and an Italian person
discussing the virtues of speaking French :-/

[steve]

On Nov 22, 4:56 am, jhofm...@google mail.com wrote:

I feel like I'm watching a Greek person and an Italian person
discussing the virtues of speaking French :-/

If your a beginner and don't understand something ask questions. If
your
an expert don't hide your knowledge, share it. I'll give you the
benefit of my doubt and won't assume which you are :)

Assumptions are the mother of all f__kups:
'Under Siege, Dark Territory'

www.beyondsql.blogspot.com

[jhofmeyr]

On Nov 23, 12:41 am, steve <rog11...@aol.c omwrote:

On Nov 22, 4:56 am, jhofm...@google mail.com wrote:

I feel like I'm watching a Greek person and an Italian person
discussing the virtues of speaking French :-/

If your a beginner and don't understand something ask questions. If
your
an expert don't hide your knowledge, share it. I'll give you the
benefit of my doubt and won't assume which you are :)

Assumptions are the mother of all f__kups:
'Under Siege, Dark Territory'

www.beyondsql.blogspot.com

I don't claim to be an expert in either Greek or Italian .. but when
I'm in Greece I try to speak Greek, and when in Italy - Italian :)

Maybe I am just too used to it, but personally I think that SQL is
excellent at performing the task it was designed to perform. The fact
that it is not the "same" as other programming languages might have
something to do with the nature of the tasks I write it to perform. I
guess when I learn new languages I try to get the most out of them as
they are, at the end of the day there is a reason why I learned the
language and usually it involves earning a pay cheque.

Maybe I am too young (or too busy) to have had many late nights
pondering how much better my life would be if some giant corporation
would ask its developers to communicate more :)

[Ed Murphy]

steve wrote:

Exactly. Think of sql strings. This table, TABLE(TABLE (M VARCHAR(15),
N VARCHAR(15)),
is a differnt type than TABLE (N VARCHAR(16), N VARCHAR(16))! This
means that we couldn't compare the two and undermines real relational
division. To declare how many characters in a string is clearly the
opposite of what the relational idea of data independence is all
about. Relationally there can only be a 'string' type having
absolutely nothing to do with its storage characteristics . And this
is the same idea in any programming language. This is just one
manifestation of how sqls design ignores the concept of a strong type.

Shouldn't you be complaining that such variables are /too/ strongly
typed? Anyway, this is a separate complaint from your previous ones
(at least those that I've seen), and IMO a minor one.

>This would allow bad developers to commit the common 'a,b,c' 1NF
violation in a whole new way, but then bad developers can screw
things up in any language.

The view that strings like 'a,b,c' violate the idea of the atomicity
of a column in an sql table is a direct result of sql's lack of types
and lack of relationships between types. There is no violation of any
kind in a relational system because the string can be stored as
a single value of a column retaining the concept that there individual
elements involved. It would simply be stored as a 'list' type.

Beyond your simple examples (which I snipped for brevity), a slightly
more interesting usage would be

select x, y -- y's type is e.g. TABLE (Z VARCHAR(15))
from the_table
where 'a' in y

or perhaps this would be better, since y might have multiple columns:

select x, y
from the_table
where 'a' in (select z from y)

This would probably have pros and cons in practice.

I don't think MS could lock its developer army in a hotel and tell
them to make sql a little more relational:) They have two choices.
Either buy a relational system (like D4) or start from the ground up
to
develop one. The gulf between a relational system and sql is too great
to try to simply make changes in sql server. Which ever major vendor
does either will 'own' application development :)

Why? The syntax extensions seem straightforward , provided that it can
be implemented reasonably efficiently.