• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 897
  • Last Modified:

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

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
MOA81
Asked:
MOA81
  • 20
  • 6
  • 5
  • +1
4 Solutions
 
alfredwhangCommented:
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
 
MOA81Author Commented:
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
 
alfredwhangCommented:
======================= 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MOA81Author Commented:
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
 
alfredwhangCommented:
it will work because the original content was string in the beginning anyway
0
 
MOA81Author Commented:
ok thanks

ill check tonight and revert later

any other ideas or code thats tested that i can test tonight
0
 
MOA81Author Commented:
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
 
MOA81Author Commented:
Hello!!!

the proposed solution is not working and other ideas?
0
 
alfredwhangCommented:

        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
 
MOA81Author Commented:
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
 
MOA81Author Commented:
Helloo??
0
 
Jeff CertainCommented:
Out of curiosity, is there a reason you're using binary instead of varchar?
0
 
MOA81Author Commented:
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
 
Jeff CertainCommented:
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
 
MOA81Author Commented:
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
 
alfredwhangCommented:
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
 
MOA81Author Commented:
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
 
Anthony PerkinsCommented:
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
 
MOA81Author Commented:
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
 
MOA81Author Commented:
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
 
MOA81Author Commented:
*I can not represent my SQL Binary field as text ??
0
 
Jeff CertainCommented:
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
 
Anthony PerkinsCommented:
>>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
 
MOA81Author Commented:
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
 
Jeff CertainCommented:
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
 
MOA81Author Commented:
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
 
MOA81Author Commented:
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
 
Jeff CertainCommented:
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
 
MOA81Author Commented:
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
 
Jeff CertainCommented:
If I had to guess, I'd look at the ASCII hex table to find out.
0
 
MOA81Author Commented:
ACSII hex table

can you send me the link if you have it
0
 
MOA81Author Commented:
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
 
MOA81Author Commented:
0x 50 61 73 73 77 6F 72 64
      P  a  s  s  w  o r  d
0
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 20
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now