May 20, 2008 at 6:08 am
If you think this should be changed, file this on Connect (connect.microsoft.com) and get people to vote for it.
May 20, 2008 at 6:20 am
Of course it should not be changed, that would be stupid.
May 20, 2008 at 6:31 am
I think there are 2 points to this question:
1) The fact the function did not throw an error about the input being truncated; and;
2) The length of the return string.
I think most people would agree on the 2nd part - that you can't expect to get 12 chars back when you define 10.
However you should not be allowed to pass any more than 10 chars into a 10 char field without getting the truncate message.
May 20, 2008 at 6:43 am
Derek Dongray (5/20/2008)
I got this wrong, because I didn't stop and think, as I've actually encountered it in the past and now always declare VARCHAR parameters as larger than I expect.Of course, what I'd really like is something like C's pointer type (char*) as an arguemnt. I think PL/1 used to be able to declare character array arguments as CHAR(*), which would accept a character arrray of any size. 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)?
- 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 7:01 am
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.
- 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 7:22 am
I don't remember the syntax off hand but there is a varchar(maxvarcharsize) that can be used.
May 20, 2008 at 7:43 am
Seems to me that it is working exactly as expected. If you define a parameter to be a certain length, then that should be what you get. I do understand the other part of the argument here that an error should be returned - to the effect that the length of the parameter being passed would be truncated. As SQL Server has always had this behavior - I would hope that if the behavior is changed/fixed (depending upon your viewpoint!) that it would be noted so as not to break any existing code that is currently relying on that behavior...
May 20, 2008 at 7:49 am
marklegosz (5/20/2008)
I think there are 2 points to this question:1) The fact the function did not throw an error about the input being truncated; and;
However you should not be allowed to pass any more than 10 chars into a 10 char field without getting the truncate message.
This is what I thought too. There should be a truncation error message, not just truncate the input and continue.
May 20, 2008 at 7:51 am
Jon Monahan (5/20/2008)
Seems to me that it is working exactly as expected. If you define a parameter to be a certain length, then that should be what you get. I do understand the other part of the argument here that an error should be returned - to the effect that the length of the parameter being passed would be truncated. As SQL Server has always had this behavior - I would hope that if the behavior is changed/fixed (depending upon your viewpoint!) that it would be noted so as not to break any existing code that is currently relying on that behavior...
This is shocking to me. I don't mean to pick on you, Jon, but 1) why would you want an error--or even a warning--telling you something you should already know and 2) why would you write a procedure accepting a limited number of characters without knowing the correct limit? This seems to be only an argument for those who don't like to think, test, or check their logic before launching their code into the wild. I'm no SQL guru or anything, but if the code didn't truncate without warning, I'd have to wonder whether anything I did worked right. This just seems too elementary.
May 20, 2008 at 8:03 am
I think there must return an error message. Same thing when you try to insert some value in a field shorter than the input.
For those who say that this behavior is correct, why the truncate must be from left to right and not the opposite.
May 20, 2008 at 8:05 am
Ryan Riley (5/20/2008)
This is shocking to me. I don't mean to pick on you, Jon, but 1) why would you want an error--or even a warning--telling you something you should already know and 2) why would you write a procedure accepting a limited number of characters without knowing the correct limit? This seems to be only an argument for those who don't like to think, test, or check their logic before launching their code into the wild. I'm no SQL guru or anything, but if the code didn't truncate without warning, I'd have to wonder whether anything I did worked right. This just seems too elementary.
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.
May 20, 2008 at 8:13 am
What this question highlights is that an error is NOT thrown when you pass a parameter value that is too long into the function.
If you tried that with an INSERT, it would fail. Why does it make sense to allow it on a function? How is passing a varchar(13) value into a varchar(10) parameter any different than passing a varchar(13) into an integer parameter?
IMHO, it should fail, or at least give a warning message. A well-written function would not "rely" on the truncation. Something like that should be handled before the function is called, OR increase the size of the parameter, and perform a Left(@bin,10) inside the function, if the purpose is to return a varchar(10).
Allowing this truncation is a crutch for sloppy code.
May 20, 2008 at 8:22 am
Carla Wilson (5/20/2008)
What this question highlights is that an error is NOT thrown when you pass a parameter value that is too long into the function.If you tried that with an INSERT, it would fail. Why does it make sense to allow it on a function? How is passing a varchar(13) value into a varchar(10) parameter any different than passing a varchar(13) into an integer parameter?
IMHO, it should fail, or at least give a warning message. A well-written function would not "rely" on the truncation. Something like that should be handled before the function is called, OR increase the size of the parameter, and perform a Left(@bin,10) inside the function, if the purpose is to return a varchar(10).
Allowing this truncation is a crutch for sloppy code.
I agree - it makes me wonder why there is a need to specify any length for the function input parameter if SQL Server won't tell you that you've exceeded it.
-------------------
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:33 am
Hey Guys,
I don't believe it is just an issue with a function. For example:
DECLARE @BIN VARCHAR(10)
SET @BIN = '1234567890ABC'
SELECT @BIN
This does not return a truncation error or warning either. Yet the previous reply with inserting into a temp table does. This does not seem to be consistent. Just my two cents...
May 20, 2008 at 8:34 am
The difference between INSERT and the function is when you insert and the data get truncated, this leads to stored data corruption. If you use a function, the stored data is not corrupted at this particular point. Please, don't argue you would use the function result to store back into the database. You do have to know what you are doing similar as you would be using Substring() or Left() functions.
Regards,Yelena Varsha
Viewing 15 posts - 16 through 30 (of 79 total)
You must be logged in to reply to this topic. Login to reply