VB6, Access database, Data encryption


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: VB6, Access database, Data encryption

Hybrid View

  1. #1
    Karen Guest

    VB6, Access database, Data encryption



    Please help,

    I am encrypting an RTF file and then building a SQL statement to insert the
    data into an Access database. The encryption is confusing the syntax of the
    SQL. I don't know exactly which symbol is causing the problem. I tried replacing
    all ' with '' and I still had my problem. I don't know what other symbols
    would cause a problem

    Is there anyway to fix this problem. Maybe I can encrypt after it gets to
    the access database. I don't even know if this is possible. If anybody knows
    please share your thoughts.

    I am using a com component from the DEVX website ENCRYPTex. It is a very
    simple component that has 4 functions.

    thanks for any help
    karen



  2. #2
    Bernie Guest

    Re: VB6, Access database, Data encryption


    "Karen" <vb.@127.0.0.1> wrote:
    >
    >
    >Please help,
    >
    >I am encrypting an RTF file and then building a SQL statement to insert

    the
    >data into an Access database. The encryption is confusing the syntax of

    the
    >SQL. I don't know exactly which symbol is causing the problem. I tried replacing
    >all ' with '' and I still had my problem. I don't know what other symbols
    >would cause a problem
    >
    >Is there anyway to fix this problem. Maybe I can encrypt after it gets to
    >the access database. I don't even know if this is possible. If anybody knows
    >please share your thoughts.
    >
    >I am using a com component from the DEVX website ENCRYPTex. It is a very
    >simple component that has 4 functions.
    >
    >thanks for any help
    >karen
    >
    >


    Hi Karen,

    Both singlequotes and doublequotes must be doubled before putting the string
    into the sql-statement. Also check that there are no Chr(0) in the string
    (shouldn't be but who knows how this encryption works). I'm not sure, though,
    if Chr(0)'s in a string mess up the processing.

    Another approach is to use the function

    ADODB.RecordSet.Update Fields, Values

    where Fields and Values are VariantArrays. By using this function you don't
    have to worry about qoutes in the string. Just open the recordset with the
    desired record and update it with

    rs.Update Array("myField"), Array(EncryptedString)

    or you can update the record like this;

    rs!myField = EncryptedString
    rs.Update

    Both ways you get rid of the quote-problem.

    Bernie

  3. #3
    karen Guest

    Re: VB6, Access database, Data encryption


    bernie,
    Thanks for you input. I want to make sure I understand.

    I have a class named CDataService that handles the database interaction.
    I declare the ADODB.connection
    --->>conavb as ADODB.connection

    I then have a function that I pass the SQL Statement. I contains the following
    statement
    ----->> conavb.execute SQLStatement,, adcmdtext

    Here are my questions?
    I am not quite sure how to ask my questions. I am new to ADO
    1. What do I need to do before the ADODB.Recordset.update? Does this command
    actually update the database or just the recordset? Or are they the same?
    It sounds like I will need to do a query into a recordset, update the recordset
    and then WHAT????

    2. How do I handle inserts because the recordset doesn't exist for me to
    update? I need to do some reading. Any suggestions?

    3. How can I pass what needs to be updated,inserted, deleted without hardcoding
    it in my CDataService. Currently, the business objects contain the specifics
    fields and values and I pass the SQLStatement to CDataService. I am trying
    to stick to a 3 tier methodology.

    Any help is appreciated. Meanwhile, I am going to do some ADO research!!!

    Karen





    "Bernie" <magnus.bernroth@rejlers.se> wrote:
    >
    >"Karen" <vb.@127.0.0.1> wrote:
    >>
    >>
    >>Please help,
    >>
    >>I am encrypting an RTF file and then building a SQL statement to insert

    >the
    >>data into an Access database. The encryption is confusing the syntax of

    >the
    >>SQL. I don't know exactly which symbol is causing the problem. I tried

    replacing
    >>all ' with '' and I still had my problem. I don't know what other symbols
    >>would cause a problem
    >>
    >>Is there anyway to fix this problem. Maybe I can encrypt after it gets

    to
    >>the access database. I don't even know if this is possible. If anybody

    knows
    >>please share your thoughts.
    >>
    >>I am using a com component from the DEVX website ENCRYPTex. It is a very
    >>simple component that has 4 functions.
    >>
    >>thanks for any help
    >>karen
    >>
    >>

    >
    >Hi Karen,
    >
    >Both singlequotes and doublequotes must be doubled before putting the string
    >into the sql-statement. Also check that there are no Chr(0) in the string
    >(shouldn't be but who knows how this encryption works). I'm not sure, though,
    >if Chr(0)'s in a string mess up the processing.
    >
    >Another approach is to use the function
    >
    > ADODB.RecordSet.Update Fields, Values
    >
    >where Fields and Values are VariantArrays. By using this function you don't
    >have to worry about qoutes in the string. Just open the recordset with the
    >desired record and update it with
    >
    > rs.Update Array("myField"), Array(EncryptedString)
    >
    >or you can update the record like this;
    >
    > rs!myField = EncryptedString
    > rs.Update
    >
    >Both ways you get rid of the quote-problem.
    >
    >Bernie



  4. #4
    Bernie Guest

    Re: VB6, Access database, Data encryption


    "karen " <vb.@127.0.0.1> wrote:
    >
    >bernie,
    >Thanks for you input. I want to make sure I understand.
    >
    >I have a class named CDataService that handles the database interaction.
    >I declare the ADODB.connection
    >--->>conavb as ADODB.connection
    >
    >I then have a function that I pass the SQL Statement. I contains the following
    >statement
    >----->> conavb.execute SQLStatement,, adcmdtext
    >
    >Here are my questions?
    >I am not quite sure how to ask my questions. I am new to ADO
    >1. What do I need to do before the ADODB.Recordset.update? Does this command
    >actually update the database or just the recordset? Or are they the same?
    >It sounds like I will need to do a query into a recordset, update the recordset
    >and then WHAT????
    >
    >2. How do I handle inserts because the recordset doesn't exist for me to
    >update? I need to do some reading. Any suggestions?
    >
    >3. How can I pass what needs to be updated,inserted, deleted without hardcoding
    >it in my CDataService. Currently, the business objects contain the specifics
    >fields and values and I pass the SQLStatement to CDataService. I am trying
    >to stick to a 3 tier methodology.
    >
    >Any help is appreciated. Meanwhile, I am going to do some ADO research!!!
    >
    >Karen
    >


    Hi again Karen,

    Using the recordset directly to update a record has the advantage of not
    being forced to assemble a SQL-statement (meaning no quotes are needed).

    This is the basics to use this method;

    The table myTable consists of 2 columns:
    ID (a counter)
    mystrField (a string column)


    Private Function UpdateRecord(ByRef EncryptedStuff As String, _
    ByRef recordID As Long)

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    'Open the connection...
    cn.Open ConnectionString

    'Open an updatable recordset with only the desired record...
    rs.Open "SELECT * FROM myTable WHERE ID=" & recordID, _
    cn, _
    adOpenKeyset, _
    adLockPessimistic

    'Check that there is a record as expected...
    If Not (rs.EOF And rs.BOF) Then

    'Either you can do like this:

    'Set the field to the new string...
    'EncryptedStuff may contain any garbage like quotes of any kind!
    rs!mystrField = EncryptedStuff
    'Update the recordset...
    rs.Update

    'or like this:
    'rs.Update Array("mystrField"), Array(EncryptedStuff)

    'Both ways give the same result!

    End If

    'Free the resources (no need to use the Close-method)...
    Set rs = Nothing
    Set cn = Nothing

    End Function


    I hope you got it. Don't forget the errorhandling, though.

    Regards

    Bernie



  5. #5
    Guest

    Re: VB6, Access database, Data encryption


    "Bernie" <magnus.bernroth@rejlers.se> wrote:
    >
    >"karen " <vb.@127.0.0.1> wrote:
    >>
    >>bernie,
    >>Thanks for you input. I want to make sure I understand.
    >>
    >>I have a class named CDataService that handles the database interaction.
    >>I declare the ADODB.connection
    >>--->>conavb as ADODB.connection
    >>
    >>I then have a function that I pass the SQL Statement. I contains the following
    >>statement
    >>----->> conavb.execute SQLStatement,, adcmdtext
    >>
    >>Here are my questions?
    >>I am not quite sure how to ask my questions. I am new to ADO
    >>1. What do I need to do before the ADODB.Recordset.update? Does this command
    >>actually update the database or just the recordset? Or are they the same?
    >>It sounds like I will need to do a query into a recordset, update the recordset
    >>and then WHAT????
    >>
    >>2. How do I handle inserts because the recordset doesn't exist for me to
    >>update? I need to do some reading. Any suggestions?
    >>
    >>3. How can I pass what needs to be updated,inserted, deleted without hardcoding
    >>it in my CDataService. Currently, the business objects contain the specifics
    >>fields and values and I pass the SQLStatement to CDataService. I am trying
    >>to stick to a 3 tier methodology.
    >>
    >>Any help is appreciated. Meanwhile, I am going to do some ADO research!!!
    >>
    >>Karen
    >>

    >
    >Hi again Karen,
    >
    >Using the recordset directly to update a record has the advantage of not
    >being forced to assemble a SQL-statement (meaning no quotes are needed).
    >
    >This is the basics to use this method;
    >
    >The table myTable consists of 2 columns:
    > ID (a counter)
    > mystrField (a string column)
    >
    >
    >Private Function UpdateRecord(ByRef EncryptedStuff As String, _
    > ByRef recordID As Long)
    >
    > Dim cn As New ADODB.Connection
    > Dim rs As New ADODB.Recordset
    >
    > 'Open the connection...
    > cn.Open ConnectionString
    >
    > 'Open an updatable recordset with only the desired record...
    > rs.Open "SELECT * FROM myTable WHERE ID=" & recordID, _
    > cn, _
    > adOpenKeyset, _
    > adLockPessimistic
    >
    > 'Check that there is a record as expected...
    > If Not (rs.EOF And rs.BOF) Then
    >
    > 'Either you can do like this:
    >
    > 'Set the field to the new string...
    > 'EncryptedStuff may contain any garbage like quotes of any kind!
    > rs!mystrField = EncryptedStuff
    > 'Update the recordset...
    > rs.Update
    >
    > 'or like this:
    > 'rs.Update Array("mystrField"), Array(EncryptedStuff)
    >
    > 'Both ways give the same result!
    >
    > End If
    >
    > 'Free the resources (no need to use the Close-method)...
    > Set rs = Nothing
    > Set cn = Nothing
    >
    >End Function
    >
    >
    >I hope you got it. Don't forget the errorhandling, though.
    >
    >Regards
    >
    >Bernie
    >
    >

    BERNIE,

    I have done what you have suggested. I think we are getting close. Now, I
    am getting errors with my RTF data (which is a string variable populated
    by TXTextcontrol activex control). I can updated my recordset with regular
    text data, but I have problems with the Txtcontrol data or encrypted data.
    I get the following error and I don't know how to determine the exact problem
    or where to look for more error messages.

    When I do this :
    -->rs!QuestionRTF = strQuestionRTF 'rtf formated not encrypted
    -->rs.update
    or
    -->rs!QuestionDesc = strQuestionDescEcypted 'regular string ecrypted
    -->rs.update

    I get this:
    ---> Multiple -step OLE DB operation generated errors. Check Each OLE DB
    status value, if available, No work done.

    When I do this

    -->rs.update array("QuestionRTF"), Array(strQuestionDescEncrypted)

    I get this:
    ----> Type mismatch

    I will continue to trouble shoot with your suggestions. I wasn't able to
    find the object that contained the OLE DB errors. I looked in the connection
    object and the recordset object. Any suggestion here

    Please stick with me Bernie.
    Thanks
    Karen






  6. #6
    Karen Guest

    Re: VB6, Access database, Data encryption


    <vb.@127.0.0.1> wrote:
    >
    >"Bernie" <magnus.bernroth@rejlers.se> wrote:
    >>
    >>"karen " <vb.@127.0.0.1> wrote:
    >>>
    >>>bernie,
    >>>Thanks for you input. I want to make sure I understand.
    >>>
    >>>I have a class named CDataService that handles the database interaction.
    >>>I declare the ADODB.connection
    >>>--->>conavb as ADODB.connection
    >>>
    >>>I then have a function that I pass the SQL Statement. I contains the following
    >>>statement
    >>>----->> conavb.execute SQLStatement,, adcmdtext
    >>>
    >>>Here are my questions?
    >>>I am not quite sure how to ask my questions. I am new to ADO
    >>>1. What do I need to do before the ADODB.Recordset.update? Does this command
    >>>actually update the database or just the recordset? Or are they the same?
    >>>It sounds like I will need to do a query into a recordset, update the

    recordset
    >>>and then WHAT????
    >>>
    >>>2. How do I handle inserts because the recordset doesn't exist for me

    to
    >>>update? I need to do some reading. Any suggestions?
    >>>
    >>>3. How can I pass what needs to be updated,inserted, deleted without hardcoding
    >>>it in my CDataService. Currently, the business objects contain the specifics
    >>>fields and values and I pass the SQLStatement to CDataService. I am trying
    >>>to stick to a 3 tier methodology.
    >>>
    >>>Any help is appreciated. Meanwhile, I am going to do some ADO research!!!
    >>>
    >>>Karen
    >>>

    >>
    >>Hi again Karen,
    >>
    >>Using the recordset directly to update a record has the advantage of not
    >>being forced to assemble a SQL-statement (meaning no quotes are needed).
    >>
    >>This is the basics to use this method;
    >>
    >>The table myTable consists of 2 columns:
    >> ID (a counter)
    >> mystrField (a string column)
    >>
    >>
    >>Private Function UpdateRecord(ByRef EncryptedStuff As String, _
    >> ByRef recordID As Long)
    >>
    >> Dim cn As New ADODB.Connection
    >> Dim rs As New ADODB.Recordset
    >>
    >> 'Open the connection...
    >> cn.Open ConnectionString
    >>
    >> 'Open an updatable recordset with only the desired record...
    >> rs.Open "SELECT * FROM myTable WHERE ID=" & recordID, _
    >> cn, _
    >> adOpenKeyset, _
    >> adLockPessimistic
    >>
    >> 'Check that there is a record as expected...
    >> If Not (rs.EOF And rs.BOF) Then
    >>
    >> 'Either you can do like this:
    >>
    >> 'Set the field to the new string...
    >> 'EncryptedStuff may contain any garbage like quotes of any kind!
    >> rs!mystrField = EncryptedStuff
    >> 'Update the recordset...
    >> rs.Update
    >>
    >> 'or like this:
    >> 'rs.Update Array("mystrField"), Array(EncryptedStuff)
    >>
    >> 'Both ways give the same result!
    >>
    >> End If
    >>
    >> 'Free the resources (no need to use the Close-method)...
    >> Set rs = Nothing
    >> Set cn = Nothing
    >>
    >>End Function
    >>
    >>
    >>I hope you got it. Don't forget the errorhandling, though.
    >>
    >>Regards
    >>
    >>Bernie
    >>
    >>

    >BERNIE,
    >
    >I have done what you have suggested. I think we are getting close. Now,

    I
    >am getting errors with my RTF data (which is a string variable populated
    >by TXTextcontrol activex control). I can updated my recordset with regular
    >text data, but I have problems with the Txtcontrol data or encrypted data.
    >I get the following error and I don't know how to determine the exact problem
    >or where to look for more error messages.
    >
    >When I do this :
    >-->rs!QuestionRTF = strQuestionRTF 'rtf formated not encrypted
    >-->rs.update
    >or
    >-->rs!QuestionDesc = strQuestionDescEcypted 'regular string ecrypted
    >-->rs.update
    >
    >I get this:
    >---> Multiple -step OLE DB operation generated errors. Check Each OLE DB
    >status value, if available, No work done.
    >
    >When I do this
    >
    >-->rs.update array("QuestionRTF"), Array(strQuestionDescEncrypted)
    >
    >I get this:
    >----> Type mismatch
    >
    >I will continue to trouble shoot with your suggestions. I wasn't able to
    >find the object that contained the OLE DB errors. I looked in the connection
    >object and the recordset object. Any suggestion here
    >
    >Please stick with me Bernie.
    >Thanks
    >Karen
    >
    >Bernie,

    I encrypted a regular text field using the array and it worked OK. The problem
    seems to stem around the RTF type data (which is a rtf text string). If I
    can figure out what that database error it would help. I didn't have a problem
    with RTF data when I was building and using the SQL statements. GO Figure

    Karen
    >
    >
    >



  7. #7
    Bernie Guest

    Re: VB6, Access database, Data encryption


    >>Bernie,

    >I encrypted a regular text field using the array and it worked OK. The problem
    >seems to stem around the RTF type data (which is a rtf text string). If

    I
    >can figure out what that database error it would help. I didn't have a problem
    >with RTF data when I was building and using the SQL statements. GO Figure
    >
    >Karen


    Hi,

    I must honestly admit that I'm not aware of any 'RTF data type'. But I guess
    you end up with some kind of binary string (everything is binary to the computer,
    right?). Maybe you should try to put it in a binary column (PM, Blob, Image
    or whatever db you're using).

    Can you give me a sample of how this encrypted stuff looks like (I hope it
    isn't too long as a string)?

    I have for a long time used strings to hold binary stuff in different db's
    with no problems, no matter what characters they are translated to.

    Bernie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center