Solved

How can I read/write SQL Binary Database field through .NET applications ??

Posted on 2007-03-28
34
786 Views
Last Modified: 2007-12-19
Dear .NET Developers, Experts

I am using SQL Server DB

also my application is VB.NET windows and also web

I have a filed in the Database which is SQL Binary

Basically what I want to do is to read this file and display it on a form or a web page in a readable format

also the ability to write to this field to be able to edit

so I want to manipulate this field read/write

what is the necessary code to do this

appreciate if you can submit the whole code

thanks in advance
0
Comment
Question by:MOA81
  • 20
  • 6
  • 5
  • +1
34 Comments
 
LVL 2

Expert Comment

by:alfredwhang
ID: 18808230
you need to know what kind of information it is keeping in the binary field...it may be jpg, mov, exe, zip etc.

so how to edit the field  would depend on what kind of data it is.  if you just want to edit them without any consideration you can use byte array.
0
 
LVL 8

Author Comment

by:MOA81
ID: 18808257
Dear alfredwhang

Well its used to store the users' passwords

can you please provide the code to do it

and can you also include the byte array
0
 
LVL 2

Expert Comment

by:alfredwhang
ID: 18808445
======================= read the field:
Dim dt as New DataTable
Dim da as new SqlDataAdapter( ... query string , ...connection string...)
da.Fill(dt)
PasswordString = dt.Rows(0)("TheBinaryField").ToString()

======================= update the field:

s = "UPDATE TheTableName SET TheBinaryField = @value"
dim SqlCmd as new SqlCommand(s, ... connection string)
SqlCmd.Parameters.Add("@value", SqlDbType.Binary).Value = Convert.ToByte(TheUpdatedPassword)
SqlCmd.ExecuteNonQuery()
0
 
LVL 8

Author Comment

by:MOA81
ID: 18808580
PasswordString = dt.Rows(0)("TheBinaryField").ToString()

are you sure .ToString() works for this case

its an SQL Binary straight to string ??

as for the update ill check and revert
0
 
LVL 2

Expert Comment

by:alfredwhang
ID: 18808642
it will work because the original content was string in the beginning anyway
0
 
LVL 8

Author Comment

by:MOA81
ID: 18808722
ok thanks

ill check tonight and revert later

any other ideas or code thats tested that i can test tonight
0
 
LVL 8

Author Comment

by:MOA81
ID: 18816372
Dear All

I tried .Tostring() it didn't work

it returned the following result in the field :    System.Byte[]

here is my code

Dim myconn As New SqlConnection()
        Dim myconnectionString As String = "Data Source=MOHD_ALI\MOA_SQL2005;" & _
                                            "Initial Catalog=Bahrain;Integrated Security=SSPI"
        Dim command As New SqlCommand()

        command.CommandText = "SELECT SY13001,SY13004 FROM SY130100 WHERE SY13001='SYS'"
' COMMENT : SY13004 IS THE BINARY FIELD

        command.CommandType = CommandType.Text
        command.Connection = myconn

        myconn.ConnectionString = myconnectionString
        myconn.Open()
        Dim thisreader As SqlDataReader = command.ExecuteReader()



        While (thisreader.Read())
            txtUser.Text = thisreader(0)
            txtPass.Text = thisreader(1).ToString
        End While

        MsgBox(myconn.State.ToString())
        myconn.Close()

        MsgBox(myconn.State.ToString())



THANKS
0
 
LVL 8

Author Comment

by:MOA81
ID: 18831370
Hello!!!

the proposed solution is not working and other ideas?
0
 
LVL 2

Assisted Solution

by:alfredwhang
alfredwhang earned 120 total points
ID: 18831566

        While (thisreader.Read())
            txtUser.Text = thisreader(0)
           dim bytes as SqlBytes = reader.GetSqlBytes(1);
           txtPass.Text = System.Text.Encoding.ASCII.GetString(bytes);
        End While
0
 
LVL 8

Author Comment

by:MOA81
ID: 18831610
Dear alfredwhang

here is the code i tried according to your last comment



While (thisreader.Read())
                txtUser.Text = thisreader(0)
                Dim bytes As System.Data.SqlTypes.SqlBytes = thisreader.GetSqlBytes(1)
                txtPass.Text = System.Text.Encoding.ASCII.GetString(bytes)
End While

but it gives the following error

Error      1      Value of type 'System.Data.SqlTypes.SqlBytes' cannot be converted to '1-dimensional array of Byte'.      

I am using VS 2005 VB.NET
0
 
LVL 8

Author Comment

by:MOA81
ID: 18835472
Helloo??
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18856942
Out of curiosity, is there a reason you're using binary instead of varchar?
0
 
LVL 8

Author Comment

by:MOA81
ID: 18857043
Well its more secure

I am using a software in my company which has this kind of setup and I want to be able to read write these fields

I read this article about MD5CryptoServiceProvider Class its really interesting but still I didn't manage to do the conversion
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18857336
I don't know anything about using binary data. However, I'd argue that your data really isn't "secure" if all you're doing is changing the format.

I've used SHA and MD5 to hash passwords -- I usually add a random-length salt and a checksum. I'd be happy to provide the code to do this, if you'd like.
0
 
LVL 8

Author Comment

by:MOA81
ID: 18857384
I guess this is the code you are talking about (its in C# but the idea counts)

private void cmdUserCreate_Click(object sender, System.EventArgs e)
{
// Create a connection
string strConnectionString;
strConnectionString = "Connection String";
SqlConnection ConnObj = new SqlConnection(strConnectionString);

//Create a command object for the query
string SQLstring ;
SQLstring = "INSERT INTO UsersList(Username,Password) VALUES(@Username, @Password)";
SqlCommand ObjCmd = new SqlCommand(SQLstring,ConnObj);

//Create User parameter
SqlParameter UserParameter = new SqlParameter("@Username",SqlDbType.VarChar, 50);
UserParameter.Value = txtUsername.Text;
ObjCmd.Parameters.Add(UserParameter);

//Encrypt the Password
MD5CryptoServiceProvider md5Hash  = new MD5CryptoServiceProvider();
byte[] hashedBytes;
UTF8Encoding encoder  = new UTF8Encoding();
hashedBytes = md5Hash.ComputeHash(encoder.GetBytes(txtPassword.Text));

//Create Password Parameter
SqlParameter PasswordParameter = new SqlParameter("@Password",SqlDbType.Binary, 50);
PasswordParameter.Value = hashedBytes;
ObjCmd.Parameters.Add(PasswordParameter);
ConnObj.Open();
ObjCmd.ExecuteNonQuery();
ConnObj.Close();
}

-------------------------

I will appreciate if you can share some code I would love to see an example of your experience

But I really want to know how to manipulate Binary Data
0
 
LVL 2

Assisted Solution

by:alfredwhang
alfredwhang earned 120 total points
ID: 18857533
i have used  http://www.freevbcode.com/ShowCode.Asp?ID=4520.  its really simple...juz call Encrypt and
Decrypt.  then your password field in the database should be char/varchar

you can also use http://www.mentalis.org/soft/projects/crypto/ which provide more varieties of encryptions.
0
 
LVL 8

Author Comment

by:MOA81
ID: 18857582
Dear alfredwhang

Thanks a lot for the input; appreciate it

I guess I will have to wait for a couple of more comments regarding the Binary read write issue

I am starting to think that its a matter of points; I guess I will raise the points maybe I can draw more attention to the experts who know how to solve this.

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 70 total points
ID: 18861920
Chaosian,

>>However, I'd argue that your data really isn't "secure" if all you're doing is changing the format.<<
I could not agree more.

MOA81,
If you insist in doing it this way, this is how you read it:

SELECT CAST(Password as varchar(20))
FROM YourTableName
WHERE ...

This is how you write to it:
Update YourTableName
Set Password = CAST('Password' as binary)
WHERE ...
0
 
LVL 8

Author Comment

by:MOA81
ID: 18864082
Dear alfredwhang, Chaosian

Both of your solutions seem to almost work but its giving me different results

the following statement
SELECT CAST(Password as varchar(20)) FROM YourTableName

returns the following result :
aqshs”””
while the password stored is:
scala

maybe we should use a different kind of cast ???
0
 
LVL 8

Author Comment

by:MOA81
ID: 18881579
Still guys I am going no where with this

any further solutions

I have tried everything

But still

I can represent my SQL Binary field as text ??
0
 
LVL 8

Author Comment

by:MOA81
ID: 18881582
*I can not represent my SQL Binary field as text ??
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18882366
MOA,

My only suggestion was to use a hash (SHA/MD5) and deal with strings, not binary. As I said earlier, if you need the code for this, I'd be happy to help.

Keep in mind that a hash is a one-way algorithm -- you can't ever find out what the original password was. You'll need to hash the input and compare it to the stored hash to make this all work.

Chaosian
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18883937
>>Still guys I am going no where with this <<
The code I posted worked find for me.  You can see for yourself here:

Declare @Password binary(16)
Set @Password = CAST('Password' as binary)
SELECT CAST(@Password as varchar(20))

Perhaps you are using Unicode

Finally, please heed Chaosian advice.
0
 
LVL 8

Author Comment

by:MOA81
ID: 18887611
Dear Chaosian

can you please provide the code

thanks in advance

I believe thats the point you can never tell what the password is god knows what kind of algorithm they are using.

Thanks
0
 
LVL 24

Assisted Solution

by:Jeff Certain
Jeff Certain earned 60 total points
ID: 18888482
Ok. This is sort of the simplest version. You'll need to import System.Web.Security.FormsAuthentication (yes, even for your Windows Form app).

The important piece of code is: HashPasswordForStoringInConfigFile(password, "MD5")

You'll need to do this both when storing the password and when comparing the input to the stored value, something like this:
Public Function ValidatePassword(input as String, actual As String) As Boolean
  Return (HashPasswordForStoringInConfigFile(input, "MD5") = actual)
End Function

Public Sub NewUser(userName as String, password As String)
  ' the rest of your sproc call here
  cmd.Parameters.Add("@userName", SqlType.VarChar, 40).Value = userName
  cmd.Parameters.Add("@password", SqlType.VarChar, 32).Value = HashPasswordForStoringInConfigFile(password, "MD5")
End Sub

IIRC, MD5 was selected because all outputs are 32 characters.
0
 
LVL 8

Author Comment

by:MOA81
ID: 18896205
Dear All

Sorry for the headache and the inconvenience I know you guys had enough from me by now

but one more last thing please;

I do the following query

select SY13004 as password from SY130100

this field is Binary(8)
I get the following result :

0x6171736873949494

which is hexadecimal

and our field is in the size of 8 so we break up the code as follows

0x                           61    71    73    68    73    94       94        94
Means Hexa            S       c      a      l       a      blank  blank  blank

no i guess things are more clearer

how can we convert these number to represent characters ?

its no longer an algorithm i guess  
0
 
LVL 8

Author Comment

by:MOA81
ID: 18896229
0x                           61    71    73    68    73    94       94        94
is 8 bytes

each byte is a character

anyway there is a function or a table to map these Hexa into the correct characters
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18897348
With hashing, just store the string as varchar. And you can't read it -- you need to hash the "proposed" password to see if the two hashes match.
0
 
LVL 8

Author Comment

by:MOA81
ID: 18897671
I did'nt quite get it

when ever i run this query

select SY13004 as password from SY130100

I always get this result

0x6171736873949494

so that mean each two digits has represent a number

can you write me the code on how to do this

Or i have to do this by trial and error and see what are the numbers for each key on the keyboard?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18898393
If I had to guess, I'd look at the ASCII hex table to find out.
0
 
LVL 8

Author Comment

by:MOA81
ID: 18907359
ACSII hex table

can you send me the link if you have it
0
 
LVL 8

Author Comment

by:MOA81
ID: 18930172
Ok its seems that the software we are using has its own set of converting characters from binary hexa to string

I found out this by create a table on a test database called it binarytable and created a field named test with dbtype sqlbinary(8)

and then inserted a line as follows
INSERT INTO [SSO].[dbo].[BinaryTable]
           ([test])
     VALUES
           (CAST('Password' as binary))

then retrieved the line as follows
SELECT CAST(test as varchar(20)) from binarytable

and it gave me the same result which is Password


Thanks to all of you for your toleration and input during this question  
0
 
LVL 8

Author Comment

by:MOA81
ID: 18930181
0x 50 61 73 73 77 6F 72 64
      P  a  s  s  w  o r  d
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18933878
You really need to re-read the EE Guidelines on Grading Standards:
What's the right grade to give?
http://www.experts-exchange.com/help.jsp#hi73

And specifically this section:
<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.
Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.

The use of a C in a vindictive manner is likely to be changed by a Moderator. You may not like the answer you get, and in some cases, and you may not like the way it is delivered, but if it is deemed to be accurate, no less than a B is an acceptable grade.
</quote>
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now