SQL Server – Modifying Inserted Data in varbinary Column

sql server

We have a table in our database for storing photos, the datatype of the actual photo data is varbinary(max).

When inserting a photo from the application, the data is correctly stored and the photo can be viewed as normal.

However, I am updating another record to have the same photo and when I update/insert the existing data, an extra leading 0 seems to be applied to the data thus breaking the decoding of the image.

When the image is inserted by the application, the binary data is correct and starts with: 0xFFD8

When I copy that binary data, and use an update/insert statement to apply this photo to another record, the data changes and starts: 0x0FFD

This means that the data cannot be converted back to an image.

Google has let me down on this occasion, though it may be that I'm not using the correct search terms.

Any assistance greatly appreciated, and if I can add any more info to assist you, in assisting me, please let me know and I'll add whatever I can.

Link to a pastebin showing data before/after insert/update: http://pastebin.com/7jrtivZ8

Best Answer

The data you pasted is exactly 65533 hex characters. Including the "0x" prefix, this is 65535 which is exactly 2^16 - 1. This strongly hints that the code that generates the hexadecimal string representation of the binary data is limited to 65535 characters. Very likely, it is using a buffer of that length.

If you are using SQL Server Management Studio to retrieve a varbinary value to use in your update query, please note that the value will be truncated according to this SSMS setting:

Tools / Options / Query Results / Results to Grid / Max Characters Retrieved / Non XML Data

This setting happens to have a maximum value of 65535. If you set this max value to, say, 31, you would get the same odd-numbered string from your example:

select * from photo -- 0xFFD8FFE000104A46494600010101 -- 29-digit hex string

This StackOverflow answer explains that the when there is an odd number of hex characters, SQL server will assume there is a leading 0. So if you write this:

update Photo set photo_data = 0xF -- a 1-digit hex string

It is functionally equivalent to writing this:

update Photo set photo_data = 0x0F

And in fact selecting it out of the table will show you it is 0x0F.

Related Solutions

Sql-server – How to distinguish the current operation is done from system or from sql server management studio

In most cases, you should be able to use some of the built-in functions:

SELECT @ModifyUser = SUSER_SNAME();

SELECT @ModifyUser = ORIGINAL_LOGIN();

This will work if they are Management Studio on their own machine and running queries remotely. If they are all using the same SQL authentication login, then in that case you should be able to tell them apart by grabbing the host name or IP address (then you'll have to figure it out based on that):

SELECT c.client_net_address, s.[host_name]
  FROM sys.dm_exec_connections AS c
  INNER JOIN sys.dm_exec_sessions AS s
  ON c.session_id = s.session_id
  WHERE s.session_id = @@SPID
  AND c.session_id = @@SPID;

In some cases, you're not going to be able to tell who it is. For example, if you let everyone log onto a common machine, access the instance through a local copy of SSMS, and they all use the same SQL authentication login, they will all look the same. There is no magic that will give SQL Server information it doesn't have. If auditing is important to you, there's a simple solution: stop letting them do that.

SQL Server – Is REPEATABLE READ Right for Validation Before Modifying Data?

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.

REPEATABLE READ is probably the right isolation level to use in this case, assuming the data you are checking does not change very often. This isolation level guarantees that rows actually read earlier in the transaction will continue to exist, unchanged, until the transaction completes by taking and holding shared locks.

A transaction running at this isolation level may miss rows that existed at the start of the transaction, but which moved during the transaction in such a way that they moved from in front to behind the seek or scan trying to locate them. It might also encounter new rows on a second run of the same query that did not exist at the beginning (phantoms).

So, in rare cases, your transaction might fail to approve an insert that would succeed if tried. It would not result in a validated insert subsequently failing, which is probably your primary concern.

If you need a more complete guarantees of isolation, consider SERIALIZABLE - but ensure the proper indexes exist to support minimal key-range locks.

Using SNAPSHOT isolation (as I mentioned on your previous question) would provide a consistent view of the data (as it appeared at the time the transaction started), but you might encounter an update conflict when trying to insert the 'validated' entry, if any concurrent modifications result in the foreign key checks failing. This would not provide the level of detail you need for a meaningful error message, so SNAPSHOT is not the right choice here.

For more information see:

The Repeatable Read Isolation Level
The Serializable Isolation Level
The ACID properties of statements and transactions

Best Answer

Related Solutions

Sql-server – How to distinguish the current operation is done from system or from sql server management studio

SQL Server – Is REPEATABLE READ Right for Validation Before Modifying Data?

Related Question