May 20, 2008 at 8:34 am
GSquared (5/20/2008)
Jan Van der Eecken (5/20/2008)
I got it wrong, in part because I didn't try it out, but also by relying on the trust I put into the SQL team, who should have thought of this. The heck, this is an invitation to hackers who exploit the EXEC statement in an SP! Colud MS pls put this on their Most Urgent To Fix Bug List? After all, I get a 'Will be truncated' message if I try to insert something into a column that's too narrow, so why should this be treated any differently?I may be missing something. How does this create a situation that could be exploited for security breaches? It's a truncation issue, not a buffer overflow. Or is there something going on here that I'm not aware of?
The only issue with this is that you can end up with unexpected results, if you think your input is "12345678909ABC" and it gets turned into "1234567890". Not a security issue, but possibly a data integrity issue.
Some SQL injection attacks depend on making parameters extra long (so that the dynamic SQL the param would go into would truncate off the WHERE clause for example (or some portion thereof), opening up a whole bunch of data you didn't see previously.)
That's why checking the length you're getting back is important....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 8:35 am
I don't think it's a glitch either.
Like most people are saying the VARCHAR(10) can only hold 10 so SQL is correct in truncating it!!!:D
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2008 at 8:38 am
As the author of this QOD, and a SQL developer NOT a DBA, I would like to thank all who entered this discussion. I think the question developed a good discussion and hopefully added to some peoples (developers) knowledge who did not post to the forum. Personally I would have expected the truncation error to be returned simply as a matter of consistency as when attempting to insert more characters into a column than the column's designed size. Thus the reference to a "glitch" - i.e., 2 or more groups of Microsoft's SQL team not fully communicating with each other.
May 20, 2008 at 8:40 am
Christopher Stobbs (5/20/2008)
I don't think it's a glitch either.Like most people are saying the VARCHAR(10) can only hold 10 so SQL is correct in truncating it!!!:D
SQL may be correct in truncating it, but then what is the point of specifying a length? Perhaps it's just an issue with the function code itself that it is not using the length of the input parameter to compare against anything?
For that matter, why should the the input parameter be checked for data type if it is not being checked for length? If it is up to the function code to handle what happens beyond 10 characters, should it not also be up to the function code to check the data type?
I'm just curious about the respective rationales, because as it is it does not make sense to me.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 20, 2008 at 8:45 am
bitbucket (5/20/2008)
As the author of this QOD, and a SQL developer NOT a DBA, I would like to thank all who entered this discussion. I think the question developed a good discussion and hopefully added to some peoples (developers) knowledge who did not post to the forum. Personally I would have expected the truncation error to be returned simply as a matter of consistency as when attempting to insert more characters into a column than the column's designed size. Thus the reference to a "glitch" - i.e., 2 or more groups of Microsoft's SQL team not fully communicating with each other.
Yes, this is a great question. I, for one, hope that you don't think that the ensuing discussion is a criticism of the quality of the QOD. Rather, I think it's a sign that this is actually a very intriguing thread resulting from a very thought-provoking QOD.
Thanks!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 20, 2008 at 8:47 am
mtassin (5/20/2008)
Try this one
Create Table #temp_test(testval varchar(10))
INSERT INTO #temp_test values('1234567890ABCD')
SELECT * FROM #temp_test
By your reasoning, I should have 1234567890 as the output.
In the case I have provided, SQL server throws a truncation error when a value is truncated. For consistencies sake, it should do the same with the function.
Well, for consistency's sake I have to agree with you... maybe. I forgot about the warning on INSERT. I never pay attention to it. I expect that if I send something that is too big, it'll get truncated. The developer really should add error handling in whatever procedure is sending data to the INSERT, FUNCTION, or whatever. Once it's passed in, a warning or no warning really shouldn't change things.
Maybe I'm missing the argument... are you saying SQL Server should be performing automatic data validation and erroring if the data doesn't match the parameters? Ooh, I would hate that. But that is, of course, just my opinion.
May 20, 2008 at 8:53 am
GSquared (5/20/2008)
Derek Dongray (5/20/2008)
If T-SQL allowed VARCHAR(*) in the declaration, you could declare a UDF which would accept a VARCHAR of any size without truncating it and it wouldn't need updating because the database fields were increased in size.Have you tried Varchar(max)?
That's actually what I quite often use. My only concern is whether there's a significant overhead. I suspect there's very little.
Derek
May 20, 2008 at 9:06 am
webrunner (5/20/2008)
Christopher Stobbs (5/20/2008)
I don't think it's a glitch either.Like most people are saying the VARCHAR(10) can only hold 10 so SQL is correct in truncating it!!!:D
SQL may be correct in truncating it, but then what is the point of specifying a length? Perhaps it's just an issue with the function code itself that it is not using the length of the input parameter to compare against anything?
For that matter, why should the the input parameter be checked for data type if it is not being checked for length? If it is up to the function code to handle what happens beyond 10 characters, should it not also be up to the function code to check the data type?
I'm just curious about the respective rationales, because as it is it does not make sense to me.
Thanks,
webrunner
How about the interpretation of declaring the parameter as VARCHAR(10) as stating that the parameter's type is VARCHAR and the function requires it truncated to 10 characters or less?create function whatever(@arg varchar(10))
returns varchar(12)
as
-- do stuff...
is equivalent tocreate function whatever(@arg varchar(max))
returns varchar(12)
as
set @arg=substring(@arg,1,10)
-- do stuff...
Derek
May 20, 2008 at 10:05 am
Ryan Riley (5/20/2008)
mtassin (5/20/2008)
Try this one
Create Table #temp_test(testval varchar(10))
INSERT INTO #temp_test values('1234567890ABCD')
SELECT * FROM #temp_test
By your reasoning, I should have 1234567890 as the output.
In the case I have provided, SQL server throws a truncation error when a value is truncated. For consistencies sake, it should do the same with the function.
Well, for consistency's sake I have to agree with you. I forgot about the warning on INSERT. I never pay attention to it. I expect that if I send something that is too big, it'll get truncated. There really should be error handling in whatever procedure is sending data to the INSERT, FUNCTION, or whatever. Once it's passed in, a warning or no warning really shouldn't change things.
Maybe I'm missing the argument... are you saying SQL Server should be performing automatic data validation and erroring if the data doesn't match the parameters? Ooh, I would hate that. But that is, of course, just my opinion.
It's not a warning... it prevents it with INSERT.
This is the result of running the code above.
No record ever goes into #temp_test
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
And yes, if you exceed the bounds of the function, the SQL engine should throw an error. Throwing more data at the function than it can handle shouldn't let you get by with just getting something back. Either the function should be fixed to accept a larger parameter, or the query calling it should be fixed to call it with the proper size of data.
May 20, 2008 at 10:19 am
I got it wrong because I paid too much attention to the function and didn't pay any attention to the parameter value passed in :blush:
But I've definately seen this plenty and I don't see it as a glitch. If my proc is using any DML I make sure my parameters are going to be at least as long as the field in the table.
May 20, 2008 at 10:21 am
So, based on this discussion ALL programming languages should then throw an error if there is a truncation occuring due to attempting to pass a string value that is larger than expected.
Sorry, but this is a normal occurance in all programming languages that I have used over the past 30+ years.
😎
May 20, 2008 at 10:47 am
Lynn Pettis (5/20/2008)
So, based on this discussion ALL programming languages should then throw an error if there is a truncation occuring due to attempting to pass a string value that is larger than expected.Sorry, but this is a normal occurance in all programming languages that I have used over the past 30+ years.
😎
1. If they did, buffer overflow and some injection attacks wouldn't work... 😎
2. SQL isn't a programming language. It is a set based Data Manipulation Language, last I checked you couldn't pause a stored procedure in the middle to ask for more input, or pop up a dynamic GUI. SQL is just a tool to get data into and out of an RDBMS.
When coding in a programming language, you can program defensively to stop buffer overflows, you can inspect parameters passed to functions. SQL apparently truncates the parameter on the way in and your function would never know.
May 20, 2008 at 11:08 am
Matt Miller (5/20/2008)
GSquared (5/20/2008)
Jan Van der Eecken (5/20/2008)
I got it wrong, in part because I didn't try it out, but also by relying on the trust I put into the SQL team, who should have thought of this. The heck, this is an invitation to hackers who exploit the EXEC statement in an SP! Colud MS pls put this on their Most Urgent To Fix Bug List? After all, I get a 'Will be truncated' message if I try to insert something into a column that's too narrow, so why should this be treated any differently?I may be missing something. How does this create a situation that could be exploited for security breaches? It's a truncation issue, not a buffer overflow. Or is there something going on here that I'm not aware of?
The only issue with this is that you can end up with unexpected results, if you think your input is "12345678909ABC" and it gets turned into "1234567890". Not a security issue, but possibly a data integrity issue.
Some SQL injection attacks depend on making parameters extra long (so that the dynamic SQL the param would go into would truncate off the WHERE clause for example (or some portion thereof), opening up a whole bunch of data you didn't see previously.)
That's why checking the length you're getting back is important....
Yeah, I can see that if you aren't using parameterized queries or aren't checking the code you run. I guess you eat the soup you cook, if you do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 12:15 pm
mtassin (5/20/2008)
2. SQL isn't a programming language. It is a set based Data Manipulation Language, last I checked you couldn't pause a stored procedure in the middle to ask for more input, or pop up a dynamic GUI. SQL is just a tool to get data into and out of an RDBMS.
When coding in a programming language, you can program defensively to stop buffer overflows, you can inspect parameters passed to functions. SQL apparently truncates the parameter on the way in and your function would never know.
I disagree with statement #2. SQL is a programming language. Yes, it is a Data Manipulation Language, but so is COBOL, FORTRAN, Pascal, Modula-2, Ada, C, C++, C#, VB.NET, et al. They all manipulate data is some way. How it does it is different.
Curious, how, inside a function, do you inspect what was passed into the function. If the the function expected a string value that is 10 characters long, how do you know that the calling program attempted to send you 15?
😎
May 20, 2008 at 1:24 pm
Lynn Pettis (5/20/2008)
I disagree with statement #2. SQL is a programming language. Yes, it is a Data Manipulation Language, but so is COBOL, FORTRAN, Pascal, Modula-2, Ada, C, C++, C#, VB.NET, et al. They all manipulate data is some way. How it does it is different.
Name one application written in 100% SQL. There are a multitude of applications written in COBOL, FORTRAN, Pascal, Modula-2, Ada, C, C++, C#, Delphi, VB.Net. Some of them even store and retrieve data without SQL.
Viewing 15 posts - 31 through 45 (of 79 total)
You must be logged in to reply to this topic. Login to reply