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.
> I'm a web programmer, but I never understood sql injecting.
Your best defense against SQL injection in SQL Server is to execute only
parameterized SQL statements and stored procedures. Never build SQL strings
by concatenating values. Code is vulnerable to injection if SQL statements
are built and executed like:
sqlStatement = "SELECT MyData FROM dbo.MyTable WHERE MyColumn = '" + myValue
+ "'";
A malicious user can change the intent of this SQL statement by specifying a
value like:
';DROP TABLE dbo.MyTable;--
or
' UNION ALL SELECT Password FROM dbo.Users;--
Google "SQL injection" for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ofiras"
news:99681e70-75a7-4a44-a4b6-cde76dd7362d@v4g2000hsf.googleg roups.com...
> 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.
On Fri, 16 Nov 2007 13:01:14 GMT, "Dan Guzman"
I agree with you, but isn't this a strike against LINQ?
-Tom.
>> I'm a web programmer, but I never understood sql injecting.
>
>Your best defense against SQL injection in SQL Server is to execute only
>parameterized SQL statements and stored procedures. Never build SQL strings
>by concatenating values. Code is vulnerable to injection if SQL statements
>are built and executed like:
>
>sqlStatement = "SELECT MyData FROM dbo.MyTable WHERE MyColumn = '" + myValue
>+ "'";
>
>A malicious user can change the intent of this SQL statement by specifying a
>value like:
>
>';DROP TABLE dbo.MyTable;--
>
>or
>
>' UNION ALL SELECT Password FROM dbo.Users;--
>
>Google "SQL injection" for more information.
On Fri, 16 Nov 2007 01:31:14 -0800 (PST), ofiras
wrote:
>I'm a web programmer, but I never understood sql injecting.
One piece worth reading:
http://www.sommarskog.se/dynamic_sql.html#SQL_injection
Roy Harvey
Beacon Falls, CT
On Nov 16, 7:41 am, Tom van Stiphout
> I agree with you, but isn't this a strike against LINQ?
That is funny, an objection to LINQ based on sql injection! :)
LINQ is to a database as asking a child to build a cyclotron. What you
get is some well intentioned but mangled piece of work that bears
little relation to reality. What a gigantic waste of resources. Had
they only brought in people who new even the basic ideas of a 'real
relational database' MS might well be on the way to breaking new
ground in an area dormat forever. Now they simply have something they
can say 'hides' sql from the net developer. It seems what was
important was to design something, anything, so long as it would
'hide' sql. If anyone can explain what ideas/principles were being
followed I'd love to hear from them. MS has a net group and a database
group. Obviously they need another.
www.beyondsql.blogspot.com
On Fri, 16 Nov 2007 19:10:51 -0800 (PST), steve
wrote:
And those two buildings cannot be more than a few hundred feet apart.
Stunning, indeed.
-Tom.
>On Nov 16, 7:41 am, Tom van Stiphout
>> I agree with you, but isn't this a strike against LINQ?
>
>That is funny, an objection to LINQ based on sql injection! :)
>
>LINQ is to a database as asking a child to build a cyclotron. What you
>get is some well intentioned but mangled piece of work that bears
>little relation to reality. What a gigantic waste of resources. Had
>they only brought in people who new even the basic ideas of a 'real
>relational database' MS might well be on the way to breaking new
>ground in an area dormat forever. Now they simply have something they
>can say 'hides' sql from the net developer. It seems what was
>important was to design something, anything, so long as it would
>'hide' sql. If anyone can explain what ideas/principles were being
>followed I'd love to hear from them. MS has a net group and a database
>group. Obviously they need another.
>
>www.beyondsql.blogspot.com
> I agree with you, but isn't this a strike against LINQ?
IMHO, yes. I know little about LINQ but, from what I've seen, there are
both pros and cons.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom van Stiphout"
news:n0brj3prd4fba5d66mcj7a35qt6pbs5gu7@4ax.com...
> On Fri, 16 Nov 2007 13:01:14 GMT, "Dan Guzman"
>
>
> I agree with you, but isn't this a strike against LINQ?
> -Tom.
>
>
>>> I'm a web programmer, but I never understood sql injecting.
>>
>>Your best defense against SQL injection in SQL Server is to execute only
>>parameterized SQL statements and stored procedures. Never build SQL
>>strings
>>by concatenating values. Code is vulnerable to injection if SQL
>>statements
>>are built and executed like:
>>
>>sqlStatement = "SELECT MyData FROM dbo.MyTable WHERE MyColumn = '" +
>>myValue
>>+ "'";
>>
>>A malicious user can change the intent of this SQL statement by specifying
>>a
>>value like:
>>
>>';DROP TABLE dbo.MyTable;--
>>
>>or
>>
>>' UNION ALL SELECT Password FROM dbo.Users;--
>>
>>Google "SQL injection" for more information.
steve wrote:
> On Nov 16, 7:41 am, Tom van Stiphout
>> I agree with you, but isn't this a strike against LINQ?
Depends. If LINQ implicitly protects against injection, then it's
precisely the opposite.
> LINQ is to a database as asking a child to build a cyclotron. What you
> get is some well intentioned but mangled piece of work that bears
> little relation to reality. What a gigantic waste of resources. Had
> they only brought in people who new even the basic ideas of a 'real
> relational database' MS might well be on the way to breaking new
> ground in an area dormat forever. Now they simply have something they
> can say 'hides' sql from the net developer. It seems what was
> important was to design something, anything, so long as it would
> 'hide' sql. If anyone can explain what ideas/principles were being
> followed I'd love to hear from them. MS has a net group and a database
> group. Obviously they need another.
Let's have a look, then, shall we?
http://en.wikipedia.org/wiki/Language_Integrated_Query#LINQ_ Code_Sample
----- begin quote -----
// the Northwind type is a subclass of DataContext created by SQLMetal
// Northwind.Orders is of type Table
// Northwind.Customers is of type Table
Northwind db = new Northwind(connectionString);
// use 'var' keyword because there is no name for the resultant type of
the projection
var q = from o in db.Orders
from c in db.Customers
where o.Quality == "200" && (o.CustomerID == c.CustomerID)
select new { o.DueDate, c.CompanyName, c.ItemID, c.ItemName };
// q is now an IEnumerable
by the compiler
foreach (var t in q)
{
// t is strongly typed, even if we can't name the type at design time
Console.WriteLine("DueDate Type = {0}", t.DueDate.GetType());
Console.WriteLine("CompanyName (lowercased) = {0}",
t.CompanyName.ToLower());
Console.WriteLine("ItemID * 2 = {0}", t.ItemID * 2);
}
----- end quote -----
Look at the comments, in particular. If the type /could/ be named at
design time, at both the database and application layer, then would
your Holy Grail have finally been achieved?
On Nov 17, 9:49 am, Ed Murphy
My point is MS is attempting to make application development easier at
the expense of database technology. There is nothing in LINQ that
advances db technology one inch. It is pure utility. There is nothing
I've read concerning LINQ that indicates that anyone remotely
connected with it has any idea of relational ideas/technology. And why
should they, it was not a requirement for the job. Had they the brains
to understand that relational technology is the key to overcoming the
impedance mismatch and leads to a simplified programming model for
application development, they may have
taken a completely different approach. Their holy grail is making sql
server invisible and what message does that send to the database
community? The day that the LINQ group recognizes the idea of a true
table type will be the day a new crew comes aboard for database
development:) I hope it's soon because net is a marvalous platform, to
good to waste on medicore thinkers.
www.beyondsql.blogspot.com
steve wrote:
> On Nov 17, 9:49 am, Ed Murphy
>
> My point is MS is attempting to make application development easier at
> the expense of database technology. There is nothing in LINQ that
> advances db technology one inch. It is pure utility. There is nothing
> I've read concerning LINQ that indicates that anyone remotely
> connected with it has any idea of relational ideas/technology....
Hmmm, is this more to your taste?
http://en.wikipedia.org/wiki/PureQuery
(Don't get fooled by the DBMS limitation... That's just beta. Eventually
anything with a JDBC driver will be accepted)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
steve wrote:
> My point is MS is attempting to make application development easier at
> the expense of database technology. There is nothing in LINQ that
> advances db technology one inch. It is pure utility. There is nothing
> I've read concerning LINQ that indicates that anyone remotely
> connected with it has any idea of relational ideas/technology. And why
> should they, it was not a requirement for the job. Had they the brains
> to understand that relational technology is the key to overcoming the
> impedance mismatch and leads to a simplified programming model for
> application development, they may have
> taken a completely different approach. Their holy grail is making sql
> server invisible and what message does that send to the database
> community? The day that the LINQ group recognizes the idea of a true
> table type will be the day a new crew comes aboard for database
> development:) I hope it's soon because net is a marvalous platform, to
> good to waste on medicore thinkers.
>
> www.beyondsql.blogspot.com
Straw man. I did not ask about what LINQ explicitly does, but
rather what it suggests:
>> Look at the comments, in particular. If the type /could/ be named at
>> design time, at both the database and application layer, then would
>> your Holy Grail have finally been achieved?
My objection is not so much to your general idea of variables of
type table-with-given-columns (I've recently worked with some systems
that could be cleaner if such a thing were available; currently they
work around it using temp tables); more to your specific use of D4 in
all your examples, as opposed to a pseudo-code extension of SQL.
On Nov 18, 10:10 pm, Ed Murphy
> .
> My objection is not so much to your general idea of variables of
> type table-with-given-columns (I've recently worked with some systems
> that could be cleaner if such a thing were available; currently they
> work around it using temp tables); more to your specific use of D4 in
> all your examples, as opposed to a pseudo-code extension of SQL.
Ok, let me directly address you dislike of D4 and your preference for
a pseudo-code extension of SQL. I'll refer to your pseudo-syntax in
the thread:
comp.databases.ms-sqlserver
'Basic Anatomy of Sql Server'
http://tinyurl.com/2olako
>Quote
Then you might want to write examples in a pseudo-syntax that
/looks/ like SQL. I know this is a matter of taste, but your
examples look ugly to me. Consider:
-- Your example of a stored procedure that returns a result set, the
-- format of which can only be deduced by reading through the code.
CREATE PROCEDURE dbo.GroupByShipCountry
@Employee Integer
AS
SELECT ShipCountry,Count(*) Cnt,Min(Freight) MinFrt,Max(Freight)
MaxFrt
FROM Orders
WHERE EmployeeID=@Employee
GROUP BY ShipCountry
-- Your example of the same stored procedure rewritten in D4.
create operator GroupByShipCountry (Employee:Integer):
table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Mon ey}
begin
result:=
Orders
where EmployeeID=Employee
group by {ShipCountry}
add{Count() Cnt,Min(Freight) MinFrt,Max(Freight) MaxFrt} ;
end;
-- My example of the same stored procedure rewritten in a
-- pseudo-extension of T-SQL.
CREATE PROCEDURE dbo.GroupByShipCountry
@Employee Integer,
@ResultSet Table (
ShipCountry varchar(15),
Cnt int,
MinFrt money,
MaxFrt money
) output
AS
SELECT ShipCountry,
Count(*) Cnt,
Min(Freight) MinFrt,
Max(Freight) MaxFrt
INTO @ResultSet
FROM Orders
WHERE EmployeeID=@Employee
GROUP BY ShipCountry
>Unquote
To begin with, the idea of a stored procedure returning a 'result' is
an sql concept. This concept does not exist in a relational (D4)
system. Relationally, a stored procedure only exists when it is
created. The execution of a sp, its runtime realization, does not
involve the definition of the procedure nor the idea of 'returning'
something from it. Relationally at runtime what sql see's as a
procedure and a result 'is' a variable of the type of the result. This
is the huge difference between the two systems. Relationally the
'@ResultSet' and the idea of inserting a query result into it is
contradictory and meaningless. The 'name' of the procedure 'is' the
variable (table), there is no result from a sp (ie. sql). Syntatically
an sql tabled value function is closer in spirit to the D4 procedure
with the big difference that the name of the table valued function is
'not' a typed variable like in D4. Finally, the sql sp makes the
distinction between identifiers as variables and non-variables using
the '@'. In D4 there is no such distinction as 'all' identitifiers
are by definition variables and the '@' is superfluous. The 'output'
declaration in the sql sp is based on the general sql idea of
'returning' something. Such a declaration is superfluous relationally
as, again. there is no concept of 'returning a something' from a 'this
sp'.
Note that LINQ realizes an sql stored procedure exactly as sql intends
it and nothing like the relational D4. The 'functional' part of
integrated query is simply how the sp is accessed within a net
language. There is no concept of a typed variable with the name of the
sp. In other words, the OR map is mapping to the same sql as if the
mapping didn't exist. MS has added a 'functional language' within net
when what it should have done is added a functional language to the
database itself! :) D4, after all, represents the functional language
of a relational system and how easy it is to use such a language
within a present day (net) programming language. The D4 answer to
overcoming the object-relational mismatch is of a totally different
nature to the one offered thru LINQ. My objection to LINQ lies in the
idea that no one at MS seems to have considered an alternative.
www.beyondsql.blogspot.com
steve wrote:
> To begin with, the idea of a stored procedure returning a 'result' is
> an sql concept. This concept does not exist in a relational (D4)
> system. Relationally, a stored procedure only exists when it is
> created. The execution of a sp, its runtime realization, does not
> involve the definition of the procedure nor the idea of 'returning'
> something from it. Relationally at runtime what sql see's as a
> procedure and a result 'is' a variable of the type of the result. This
> is the huge difference between the two systems. Relationally the
> '@ResultSet' and the idea of inserting a query result into it is
> contradictory and meaningless. The 'name' of the procedure 'is' the
> variable (table), there is no result from a sp (ie. sql).
You're being highly unclear again. You may have a good point
somewhere in here, but the communication barrier is so high
that I can't find it.
Your previous complaint about SQL stored procedures was that
there's no one part of them that unambiguously defines what
its result set(s) (zero/one/many) will look like; it requires
parsing the code, and may in fact vary depending on the execution
path taken during any given run. I suggested moving the conceptual
role of "share data with whoever called me" away from these untyped
result set(s), and into a strongly typed (table-typed) output
variable, to address this specific concern. Calling that variable
"@ResultSet" may have confused that issue; it is not a result set
as currently used in SQL, but rather a parameter that fills the
conceptual role currently filled by result sets.
I recently discovered that T-SQL has had TABLE variables since 2K:
http://www.odetocode.com/Articles/365.aspx
I had not had occasion to use them before, but it turns out that they
look just like my hypothetical extension. They can't be passed
between procedures, but if they could be, then would that be what
you're looking for?
> The 'output'
> declaration in the sql sp is based on the general sql idea of
> 'returning' something. Such a declaration is superfluous relationally
> as, again. there is no concept of 'returning a something' from a 'this
> sp'.
Let's look at your example again:
create operator GroupByShipCountry (Employee:Integer):
table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Mon ey}
begin
result:=
Orders
where EmployeeID=Employee
group by {ShipCountry}
add{Count() Cnt,Min(Freight) MinFrt,Max(Freight) MaxFrt} ;
end;
This looks like a stored procedure that returns something (except
that, unlike a SQL stored procedure, this one states up front
what that something will look like). Is there more to it than
that? Are you trying to say something about the difference between
procedures and functions? (SQL has functions, too; procedures can
alter data, functions can't.)
On Nov 20, 2:57 am, Ed Murphy
Hello Ed,
I appreciate your questions. I think some of things that bother
you have been brought up by others especially those who have a lot
of experience in sql. Are you an sql expert?:) I think if you continue
to investigate the relational model a light will go on and all of
a sudden it will make perfect sense:) I hope you stay with it.
Let me try to cut thru the semantics with a few examples that I
hope will clarify some of the points I'm trying to make.
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. At runtime the procedure is a variable of
a particular type and has a specific value based on input arguments.
An sql sp has no such nature and behaves in an entirely different way.
create procedure SqlOne
@Y int
AS
SELECT COUNT(*) AS CNT,SUM(FREIGHT) AS SUMFRT
FROM ORDERS
WHERE EMPLOYEEID=@Y
SELECT *
FROM ORDERS
WHERE EMPLOYEEID=@Y
Therefore the idea that an sql procedure can return multiple results
is meaningless if the sp is realized as a variable where only a single
result makes sense. Add to this the idea of type where each result is
a different type and the difference between sql and relational should
be even clearer. Again the relational procedure is realized exactly
like the int @X. No programming language chooses among possibe
multiple definitions of the value of a variable. It would be
equivalent to:
DECLARE @X INT
SET @X=5 or @X=10
which makes no sense. It is because the sql sp is not realized as a
variable that multiple results 'can' be returned.
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:Integer,B:Integer)
begin
result:=row(5 A,10 B);
end;
In this case at runtime D4Two is a variable of type row with 2 scalar
columns.
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. It behaves like no
other 'variable' in any programming language on the face of the
planet:) If it doesn't quack like a duck, doesn't behave like a duck,
doesn't waddle like a duck it sure as hell isn't a duck:) What MS
calls a table variable is surely not a table variable as the idea
exists in any programming language or relationally. Whatever one wants
to call an sql table the table variable is the same thing. Its
phyiscally implementation may be different but that does not change
the fact it is not a variable of a specific table type.
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').
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:)
best,
steve
www.beyondsql.blogspot.com
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:Integer,B:Integer)
> 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.)
On Nov 22, 1:34 am, 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:Integer,B:Integer)
> > 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 :-/
On Nov 22, 4:56 am, jhofm...@googlemail.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
On Nov 23, 12:41 am, steve
> On Nov 22, 4:56 am, jhofm...@googlemail.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 :)
On Nov 21, 6:34 pm, Ed Murphy
Hello Ed,
>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))?
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.
LINQ is an attempt to hide the fact that:
DECLARE @N VARCHAR(5),@M VARCHAR(6),@P VARCHAR(5),@Q VARCHAR(6)
represents 4 different types. This is but one simple form of the idea
that sql guarantees impedence mismatch! And having the choice of
changing the database or the access to it, MS chose access to the
database (LINQ). The sql community seems to not consider how bizarre
and confounding things like this look to developers coming to sql for
the first time. It must be force of habit blinding a more critical
look at how things are. There are so many that sql defeats the idea of
strong types that it would be better and easier to build a new
database system for application development. And have sql available
for everything else:)
>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)))
The relational database emphasizes types to define structures that can
be used to model processes, ie.
create table T1
{
A:Integer,
B:String,
T:row{X:Integer,Y:String},
S:row{B:Integer,C:list(String)}
key{A}
};
This is possible thru system provided types and user defined types.
It's also supported thru explicit conversion processes between one
particular type and another. The foundation to support these
constructs is unique to a relational system and does not exist in sql.
Whether a specific struture makes logical sense and whether it's
supported is another matter. Tables within tables is open to question.
Even if the system supports it would it make sense or would another
type of structure be more appropriate? This question goes to the edges
of a relational system and I'm afraid I can't do it justice here:)
>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.
For example column B of table TableList takes a comma delimited string
and splits it into a list type.
create table TableList
from
table
{
row{1 A,'A,B,C'.Split({','}) B},
row{2,'D, E , F, G'.Split({','})},
row{3,'H,I,J,K'.Split({','})}
};
The table definition of TableList is:
create table TableList
A:Integer,
B:list(String),
key{A}
For each row of the table column A can be retrieved and the individual
items of column B, the list, are availiable.
Select the value of A and the value of the 1st item in the
list(B).
select TableList {A,B[0] ListItem1};
A ListItem1
- ---------
1 A
2 D
3 H
Directly address the 1st item in the list of A=2.
select TableList[2].B[0];
D
Directly address the last item in the list of A=3.
select TableList[3].B[TableList[3].B.Count()-1];
K
Get column B for the row where A=1 and convert the list into a table.
select ToTable(TableList[1].B,'Str','Index');
or
select ToTable( (TableList where A=1 over{B})[].B, 'Str','Index');
Str Index
--- -----
A 0
B 1
C 2
It's types that a relational system guarantees integrity for and high
level operators that allow the explicit conversions between that
developers should have for application development. And this is the
same idea the MS net team calls 'functional programming' which is what
they developed LINQ for. But a relational system 'is' functional
programming!:)
>bad developers can screw things up in any language.
Sure but application development with sql has a tendency to make
anyone a nitwit at some time or other. I'm for less nitwits :)
>> 'where' and 'having').
>I'd guess these are both for efficiency.
GROUP BY was added after the original SELECT but instead of
redesigning the language they saw no problem with leaving in two
constructs that do the same thing! Ever time I see an MS paper on
'best practices' I have got to laugh:)
On table variables:
>But you agree that (1) it has some features of variables, and (2) it
>could reasonably be extended to have more features of variables?
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 :)
>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.)
Sure at least a dozen people who write books and articles find sql
straightforward:) For the rest I'd like to at least see a choice for
them. Again, I hope some can get beyond just syntax to grasp what
a relational system would offer.
best,
steve
www.beyondsql.blogspot.com
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.