saving base64string to access text field

[ghost807]

i am trying to save a text string that has been hashed using sha1 service provider.
when i try to store the hash to the database i get a sql error stating
"Syntax Error In UPDATE Statement"
yet i am using the exact same lines of code to do updates else where in the program.
my only clue is the base64string conversion.
here is the hash code.

        Dim SHA1 As SHA1CryptoServiceProvider
        Dim bytValue() As Byte
        Dim bytHash() As Byte

        ' Create New Crypto Service Provider Object
        SHA1 = New SHA1CryptoServiceProvider

        ' Convert the original string to array of Bytes
        bytValue = _
         System.Text.Encoding.UTF8.GetBytes(txtNewPass2.Text)

        ' Compute the Hash, returns an array of Bytes
        bytHash = SHA1.ComputeHash(bytValue)

        SHA1.Clear()

        ' Return a base 64 encoded string of the Hash value
        z = Convert.ToBase64String(bytHash)

and here is the udpate command, it's not pretty yet but it will be when i'm done.

        Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
        Dim conn As New OleDbConnection(connect)
        Dim adapter As New OleDbDataAdapter
        Dim sqlString As String = "SELECT * FROM tblEmployees" 
        adapter.SelectCommand = New OleDbCommand(sqlString, conn)
        Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)
        Dim BarCodeUpdate As New Database

        Try
            conn.Open()

            ' update the record
            adapter.TableMappings.Add("Table", "tblEmployees")
            adapter.Fill(BarCodeUpdate.Tables("tblEmployees"))

            Dim idx As Integer
            'Dim nullVal As DBNull
            For idx = 0 To BarCodeUpdate.tblEmployees.Rows.Count - 1
                'If BarCodeUpdate.tblMovie(idx).BarCode.ToUpper = BarCode Then
                If BarCodeUpdate.tblEmployees(idx).Employee = txtUserName.Text.ToUpper Then
                    BarCodeUpdate.tblEmployees(idx).Password = z
                End If
                'End If
            Next

            adapter.Update(BarCodeUpdate)
            BarCodeUpdate.Dispose()
            adapter.Dispose()
            conn.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

 

[ThatRickGuy]

I'm going to take a guess that your encrypted string has a ' or " character in it. Try doing that funky bracket thing [] that access does.

-Rick

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

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[ghost807]

well i did some more lookin and i didn't see any funny char added although there is an = at the end of every string.
i guess i will try to remove that and see if that helps, but i don't beleive that should make a difference.

 

[ThatRickGuy]

Just for a test, I opened up acces, made a table with a text field. Then made a new query, set it to an Update Query, and tried adding the following values:

1234!
1234=
1234'
1234"
12"34

Both 1234" and 12"34 failed.

1234" failed with "The expression you entered has an invalid string"

12"34 failed with "The expression you entered contains invalid syntax"

-Rick

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

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

I did just get "123""" to work though. It updated the value to 123"

do a string.replace(""",""""") before trying to save. You might have to double check the number of "s there too.

-Rick

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

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[ghost807]

well i looked for any ' and " and replaced = with nothing
and i still cannont get the update command to work.
when i look at it with a stop point in place this is the text i get from the watch window

2jmj7l5rSw0yVb/vlWAYkK/YBwk=

i don't beleive this should so hard to do.
basically i copied this code directly from the msdn web page and the hashing works like a champ,
just saving it sux

 

[ThatRickGuy]

After playing arround in access a little bit. It is the = on the end of the string. Try placing "s on either side of the string, so that it gets stored as "2jmj7l5rSw0yVb/vlWAYkK/YBwk="

-Rick

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

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[chiph]

Use ADO.NET parameter objects to get around this problem. Using them has been covered here a couple of times in reference to another problem people sometimes have -- SQLInjection.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ghost807]

i guess i don't understand. i've done some looking here on tek tips and i keep seeing the same things over and over again.
use ado.net parameter objects, but i never see and example.
i will do some more looking on the web but any help or examples you can give me would be great.


As a side note i have tried to surround the string with quotes
ch(34) & string & chr(34)
but i still have the same problem.

 

[ThatRickGuy]

Dim conSQL As New SqlClient.SqlConnection()
Dim comQuery As New SqlClient.SqlCommand()
Dim intAffected As Integer

comQuery.CommandType = CommandType.StoredProcedure
comQuery.CommandText = uspName

' Setup the database connection
conSQL.ConnectionString = ConnectionString
conSQL.Open()
comQuery.Connection = conSQL

' Setup all the parameter values for the stored procedure
AssignParameters(uspName, comQuery.Parameters)


'Loop through a collection of parameters
For Each parm In Parms
 If Not parm.Name Is Nothing Then
  comQuery.Parameters.Add(parm.Name, parm.Type, parm.Size).Value = parm.Value
  If Not parm.InOut = 0 Then
   comQuery.Parameters(parm.Name).Direction = parm.InOut
  End If
 End If
Next

' Execute the stored procedure
intAffected = comQuery.ExecuteNonQuery()

This is a slightly abreviated version of our SQL Server update/insert code. The Parms object is a collection of custom Parm objects that stores the parameters shown in this sub.

-Rick

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

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[ghost807]

i'm not sure how this is going to help me but i'm going to ask some more questions first.
I am connecting to an access database, in this example you are telling me to do a sql connection. is this correct?
i have never used a connection like your describing so some of the terms your using do not make sense to me and when i paste this code into my code i have errors with the following lines

comQuery.CommandText = uspName <- what is this?

AssignParameters(uspName, comQuery.Parameters)
this whole line i have problems with

For Each parm In Parms
what is parm an alias of?
what is parms an alias of?

im kind of a newbie when it comes to vb.net so thanx for your patience.
i may have more questions later but thanx for the answer so far.

 

[ThatRickGuy]

I am connecting to an access database, in this example you are telling me to do a sql connection. is this correct?

Since you are using Access, you'll want to use OLE data objects instead of SQL data objects.

comQuery.CommandText = uspName <- what is this?

That is the name of a stored procedure. You'll want to set your command type to text and the command text to the SQL to use for the update.

AssignParameters(uspName, comQuery.Parameters)
this whole line i have problems with[/quote

sorry, remove that line. The next block of code was in that sub, when I moved the code to this sub, I fergot to remove that line.

For Each parm In Parms
what is parm an alias of?
what is parms an alias of?

re-read the end of the previous post. The Parm class is a class I use to track parameters. It contains 5 properties (Name, Type, Size, Value, InOut) that specify everything about the parameter. Parms is a collection of these.

This code was not ment to be a solution for your problem. It is ment to be a place to start looking. You will need to take some understanding of what I posted, and adapt it to your situation.

-Rick

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

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[ghost807]

ok i'm not sure what's wrong now.
i am lookin at the data that is being imported into the database.
i thought i would just see if i was having a problem with the data that was being entered or if there was a problem with the code behind it.
well it looks like there is a problem with the code.

        Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
        Dim conn As New OleDbConnection(connect)
        Dim adapter As New OleDbDataAdapter
        Dim sqlString As String = "SELECT * FROM tblCashier WHERE Employee = " & Chr(34) & Cashier & Chr(34)
        adapter.SelectCommand = New OleDbCommand(sqlString, conn)
        Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)

        Try
            conn.Open()

            Dim CashierNotes As New Database
            ' update the record
            adapter.TableMappings.Add("Table", "tblCashier")
            adapter.Fill(CashierNotes.Tables("tblCashier"))

            Dim idx As Integer
            For idx = 0 To CashierNotes.tblCashier.Rows.Count - 1
                If CashierNotes.tblCashier(idx).Employee = Cashier Then
                    CashierNotes.tblCashier(idx).Employee = Cashier
                    CashierNotes.tblCashier(idx).Password = "txtNewPassText"
                    CashierNotes.tblCashier(idx).AccesLevel = CashierNotes.tblCashier(idx).AccesLevel
                End If
            Next
            adapter.Update(CashierNotes)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

but i just can't figure it out!

rick i'm hopin you see somethin i'm doing wrong because right now i'm just frustrated(s/p?)
i think i'm going to give up for the night.
thanx for your help so far.

i'm going to look into using ADO.NET parameter objects more but right now i'm just not understanding them either.
time to get more books.

 

[ghost807]

sorry the password field is the item i have been having problems with since the begining.
so....... this time i just tried to imput some text into it.
nothing do difficult for it. but i still have a problem
syntax error in update command

 

[ghost807]

ok last item. the password name seems to be reserved by SQL.
once i changed the field name in access to passcode it works just fine.

this is the final code for now, until i learn to use sql params

Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
Dim conn As New OleDbConnection(connect)
Dim adapter As New OleDbDataAdapter
Dim sqlString As String = "SELECT * FROM tblEmployees"
adapter.SelectCommand = New OleDbCommand(sqlString, conn)
Dim cmdBuilder As New OleDbCommandBuilder(adapter)
Dim BarCodeUpdate As New Database

Try
   conn.Open()

   ' update the record
   adapter.TableMappings.Add("Table", "tblEmployees")
             adapter.Fill(BarCodeUpdate.Tables("tblEmployees"))

    Dim idx As Integer
    For idx = 0 To BarCodeUpdate.tblEmployees.Rows.Count - 1
        If BarCodeUpdate.tblEmployees(idx).Employee = txtUserName.Text.ToUpper Then
            BarCodeUpdate.tblEmployees(idx).Password = Convert.Base64String(bytHash)
        End If
    Next

    adapter.Update(BarCodeUpdate)
    BarCodeUpdate.Dispose()
    adapter.Dispose()
    conn.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try

thanx for your help

 

[ThatRickGuy]

Maybe I'm just paranoid, but I've always disliked using the word "Password" for a field name or variable. I always figured if the code was comprimised, the first thing people would look for is the authentication code, putting the word Password in, just makes it that much easier on them

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.