INSERT BYTE[] TO Sql Without parameter

Question

I want to make a raw SQL query that adds an encrypted value (byte[]) to an SQL column (varbinary) without using parameter like this:

 byte[] value = GetEncryptedValue();
 string query = "INSERT INTO Table1 VALUES('" + value.ToString() + "')";

the column datatype that I want insert into is varbinary. I need some function that uses value.ToString() instead. How to write this ? i just need function like master.dbo.fn_varbintohexstr in sql!!!

@usr it can be useful. For example, you need to generate INSERT ... from existing data and apply to some other DB — T.S.
– T.S., Commented Nov 24, 2020 at 20:48

Guffa · Accepted Answer · 2013-03-03 15:42:33Z

9

A binary literal takes the form 0x6ABCDEF, i.e. 0x followed by bytes in hexadecimal form.

You can use this to turn the byte array into a literal value:

string literal = "0x" + String.Join("", value.Select(n => n.ToString("X2")));

There are not apostrophes around the binary literal:

string query = "INSERT INTO Table1 VALUES(" + literal + ")";

edited Mar 3, 2013 at 15:42

answered Mar 3, 2013 at 15:27

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Guffa Over a year ago

@usr: The OP specifically asked for a solution without using a parameteried query.

usr Over a year ago

Did not see that. What an odd requirement and he's probably mistaken with that. I fixed a typo in your code.

Guffa Over a year ago

@user1704457: Try with the code as it looks now, with 0h in the code, not 0x. That was an edit made by usr.

usr Over a year ago

"SELECT 0h12345f" does not work. Is it supposed to? "SELECT 0x12345f" does.

usr Over a year ago

This question will teach me not trying to work on such vague and misguided questions. It is unclear to me if the OP even wants this solution. He just said he wants a T-SQL solution if I interpret his rude and vague statement correctly. We are all wasting our time here.

|

T.S. · Accepted Answer · 2020-11-24 21:21:00Z

0

Another way to do it, is by using BitConverter

byte[] value = GetEncryptedValue();
string query = 
    "INSERT INTO Table1 (c1) VALUES(0x" + BitConverter.ToString(value).Replace("-", "") + ")";

BitConverter.ToString returns something like 01-02-03....

answered Nov 24, 2020 at 21:21

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Collectives™ on Stack Overflow

INSERT BYTE[] TO Sql Without parameter

2 Answers 2

15 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related