Mixed feelings over the new truncation error

2

March 20, 2019 by Kenneth Fisher

As of SQL 2019 CTP2.0 or SQL 2017 CU12 Microsoft has given us a long-awaited addition to the truncation error. Yay! But …

Yes, the new error is awesome! It gives us information about exactly what column isn’t big enough and what data is going to be truncated. And everyone’s been very excited about this. Myself included. On the other hand ..

You know what, here’s a quick demo.

Using Randolph West’s (b/t) post String or binary data would be truncated: get the full picture in SQL Server 2017 I was able to turn the error on on my SQL 2017 instance. (Although in this case I just used the trace flag on the INSERT statement. Did you know you could put a trace flag on just an insert statement? I didn’t. Pretty cool though.)

CREATE TABLE TruncationTest (SSN char(9));
GO
INSERT INTO TruncationTest VALUES ('123-45-6789')
OPTION(QUERYTRACEON 460);
GO

Msg 2628, Level 16, State 1, Line 7
String or binary data would be truncated in table ‘Test.dbo.TruncationTest’, column ‘SSN’. Truncated value: ‘123-45-67’.

Very cool! We now know that the error was on the SSN column of the table TruncationTest. Of course, we also know the first 9 characters (the actual length of the column) of what someone was putting in for their social security number. That’s the part that worries me. This is potentially a security hole. Probably easy enough to avoid, but then again, so is SQL Injection.

Basically, I’m just saying, I’d be very careful about what data is on the instance if you are going to use this feature. You know .. just in case.

Oh, and just FYI, Dynamic Data Masking doesn’t appear to affect the output. It makes sense since the information is not in the column yet. Given that I’m also guessing that any encryption on the column isn’t going to make a difference either.

2 thoughts on “Mixed feelings over the new truncation error

  1. Anna says:

    This is a great example – thank you! Wondering if MS is going to fix it..

    • Oh I don’t know that it needs to be “fixed”. With it being on a trace flag people just need to be careful when and how they turn it on. i.e. Don’t just turn it on globally in production. Maybe briefly to test a problem import and then turn it right back off again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,087 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: