Parameter truncation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parameter truncation

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 10:18:08

We had truncation of a varchar parameter today, which is causing me to worry about our processes to prevent it happening again. Any suggestions?

In the code below the parameters to TEST051009_Save_B (comment marked in Red) can be set to one less, or one more, than the correct size.

'One less' will "truncate" the data - silently. Sproc B will be called from Sproc A with "over length" parameters which are truncated.

'One more' will cause a run time error in the INSERT - that seems like a reasonable idea

Suggestions on how to safeguard catch overly-long data being presented to an SProc would be appreciated (right now my only plan is to make all VARCHAR parameters a size +1 over the actual size, and then either LEN() check them, or let an INSERT/UPDATE fail.

However, that does not remove the problem of one SProc calling another where the second has (accidentally) got shorter parameters than the first.

Kristen


CREATE TABLE TEST051009
(
	MyCode		varchar(10),
	MyDescription	varchar(20),
	PRIMARY KEY
	(
		MyCode
	)
)
GO

CREATE PROCEDURE TEST051009_Save_B
	@MyCode		varchar(10),	-- Change these to 9, 10 and 11
	@MyDescription	varchar(20)
AS
	INSERT INTO TEST051009
	(
		MyCode, MyDescription
	)
	VALUES
	(
		@MyCode,
		@MyDescription
	)
GO

CREATE PROCEDURE TEST051009_Save_A
	@MyCode		varchar(11),	-- NOTE: These are too small
	@MyDescription	varchar(21)
AS
	EXEC TEST051009_Save_B @MyCode=@MyCode, @MyDescription=@MyDescription
GO

EXEC TEST051009_Save_A 'A23456789',   'A234567890123456789'
-- The 'B' rows will be silently truncated - no warning
EXEC TEST051009_Save_A 'B234567890',  'B2345678901234567890'
-- The 'C' rows will SHOULD be truncated - some sort of error would help
EXEC TEST051009_Save_A 'C2345678901', 'C23456789012345678901'

GO

SELECT	* 
FROM	TEST051009
GO

DROP PROCEDURE TEST051009_Save_B
GO
DROP PROCEDURE TEST051009_Save_A
GO
DROP TABLE TEST051009
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-19 : 11:13:51

well, we make sure something upstream of the proc is validating length (api, gui, ws, etc.) However, I guess the only way to determine if the value sent to the proc is longer than the column its eventually going into is the method you suggested. I can't think of another alternative.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 11:41:24


CREATE PROCEDURE TEST051009_Save_B
	@MyCode		varchar(10+1),	-- Change these to 9, 10 and 11
	@MyDescription	varchar(20+1)
AS
...

Server: Msg 170, Level 15, State 1, Procedure TEST051009_Save_B, Line 3
Line 3: Incorrect syntax near '+'.

varchar(8000) here I come

Kristen

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 14:17:46

I've had couple of ideas, not sure if they would bare fruit.

Mostly our varchar(nn) parameters are restricted to a few finite lengths - 10, 20, 30, 50. Perhaps we could create a type alias for those that was varchar(8000) in Dev to try to let oversize data through - that wouldn't stop us attempting to pass a varchar(50) to a varchar(30) though [i.e. if everything is aliased to varchar(8000)!]

So Id also need a constraint that tested the LEN()

Is there something I can do that will be "compiled out" in runtime?

IF TestLength(@MyParameter, 50) IS NOT NULL RAISERROR(...)

where TestLength(@MyParameter, 50) would return NULL in a production system and have a 0 CPU impact (or very close to it)

Any merit in that line of thinking?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources