Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Decrypt All Records in a Table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Decrypt All Records in a Table

    I hope somebody can help.

    I just took over an application where my predecessor encrypted a couple of fields in a basic table (e.g. Date of Birth, Phone #). I don't need those fields encrypted at this point and I want to be able to display the decrypted data, on a Grid.

    I have the encryption key and can easily decrypt a single field in a single row, with "a5_decrypt_string(getDOB,"mykey")" in XBasic. I just added a Button to the rows of the Grid, which calls this function as an Ajax Callback (it works perfectly. I even added a "Grid Refresh" to the Action JavaScript, so the decrypted data appears for that row is a second or 2):
    function decrypt as c (e as p)
    Dim cn as sql::connection
    Dim args as sql::arguments
    Dim sqlCommand as c
    dim DOBdecrypt as c
    dim enc_str2 as c
    dim IdNum as n

    ' Get the Record Number and Decrypt the "DOB" field

    IdNum = e._currentRowDataOld.ID;
    DOBdecrypt = e._currentRowDataOld.DOB;
    enc_str2 = a5_decrypt_string(DOBdecrypt,"mykey")

    args.set("NewDOB",enc_str2);
    args.set("IdNum",IdNum);

    'debug(1)

    ' Update the SQL Table record by adding the decrypted data into a field I created called DOB2

    flag = cn.open("::Name::localClients")
    sqlCommand = "UPDATE clients Set clients.DOB2 = :NewDOB WHERE ID=:IdNum;"
    flag = cn.execute(sqlCommand, args)

    cn.close()
    end function

    But here's my request: There are more than 1,200 clients in this table/Grid and I really don't want to have to click the Button 1,200+ times.

    Is there a way to go through all of the rows in this table and add the decrypted information into the "DOB2" field? My formula is simple, because I can just use the "currentRowData". So, I think I need to be able to run the UPDATE command and then go to the "next row" and do it again, until all of the rows are decrypted.

    I'd appreciate any help or suggestions. Thanks

    Phil



    #2
    https://documentation.alphasoftware....Statements.xml
    That should help, unless someone steps up and literally writes it for you...(I could write it incorrectly - no problem!)
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

    Comment


      #3
      This should do it, even though it's not tested because I'm not ambitious enough to create the table today.
      The code assumes you have enough ram to hold the 1200 records and that the clients table has the id and dob fields.
      The code can be easily adjust to handle any other fields you need to decrypt. (I used the dob2 only because you used it);
      As a precautionary measure, you might want to try this on a smaller number of records in a different table.
      Just create a new table selecting x number of records from clients, then change clients in the code below to the new table name.


      Code:
      function decrypt as c (e as p)
      Dim cn as sql::connection
      Dim args as sql::arguments
      Dim sqlCommand as p
      dim DOBdecrypt as c
      dim enc_str2 as c
      dim IdNum as n
      dim dataArray[0] as p
      ' Get the Record Numbers and encrypted DOB
      
      sqlCommand.getData = <<%txt%
      select idnum,dob
      from clients
      %txt%
      sqlCommand.writeData = <<%txt%
      update clients
      set clients.DOB2 = :newDOB
      where id = :IDNUM
      %txt%
      
      flag = cn.open("::Name::localClients")
      flag = cn.topropertyarray(sqlCommand, args,dataArray)
      
      for each record in dataArray
      record.dob2 = a5_decrypt_string(record.dob,"mykey")
      args.set("IdNum",record.idnum)
      args.set("NewDOB",record.dob2)
      flag = cn.execute(sqlCommand.writeData,args)
      next 'each record
      ' Update the SQL Table record by adding the decrypted data into a field I created called DOB2
      
      cn.close()
      end function
      Gregg
      https://paiza.io is a great site to test and share sql code

      Comment


        #4
        You could consider doing this in a view. decrypting on the fly. I have the same need to generate a report that produces the unencrypted data and that works for me. You can then use the view to build the grid/list. You can also use the view to do an "insert into"
        Last edited by glenschild; 01-16-2021, 06:46 PM.
        Glen Schild



        My Blog

        Comment


          #5
          Originally posted by glenschild View Post
          You could consider doing this in a view. decrypting on the fly. I have the same need to generate a report that produces the unencrypted data and that works for me. You can then use the view to build the grid/list. You can also use the view to do an "insert into"
          Glenn,
          How do you use a5decrypt in a view?
          Gregg
          https://paiza.io is a great site to test and share sql code

          Comment


            #6
            Okay, I admit I have assumed that the encryption was done on the database and that the database is ms sql server. If that assumption is correct you build the sql view and to decrypt use

            SELECT

            CAST(decryptbypassphrase('decypt_key_here',fieldname here) as VARCHAR(10) ,
            FROM tablenamehere

            decryptbypassphrase() is a built in sql function
            Glen Schild



            My Blog

            Comment


              #7
              ok. I didn't think a5encrypt/a5decrypt could be used directly in sql.
              Gregg
              https://paiza.io is a great site to test and share sql code

              Comment


                #8
                First and foremost, THANK YOU Charles, Glen and Gregg! It was so kind of you to take time to help me through this. The Alpha community is go generous to give time to other developers. I hope you know how much we appreciate it.

                I decrypted the fields, perfectly (actually, there were 2 fields I needed... I used only 1 in my example to simplify the request). Because it seemed like it would be faster and easier, I tried Glen's command in a SQL Server view. I didn't get any errors, but it didn't decrypt the field. I suspect the "a5decrypt" function uses a different methodology than SQL Server's "decryptbyphrase" function. It's also possible that I formatted the command incorrectly, but it didn't work for me, so I tried the code that Gregg shared (THANK YOU, again, for doing that. I haven't had experience with the "loop" function that you and Charles suggested, but I can see the power of that array, function, now).

                When I added the code and ran the function, I got some Alpha errors. But, once I dissected the code and made a couple of changes, it worked perfectly. I know Gregg was concerned about the RAM and whether all of the changes could be handled at once, but it was perfect. As it turned out, there were 4000+ records to go through, and it decrypted the entire file, in less than 30 seconds (I did NOT do this on a live server... I used my Developer Edition on my computer (i7 with 16GB RAM).

                In case someone else can use it, I've posted the revised code, below (with the revisions highlighted and comments, where needed).

                I could not have done this without you... so, thanks again. This saved me hours of work, if I had to decrypt 4,000+ records, one-at-a-time (actually, it saved my wife the time... I was going to assign the "button pushing" to her ).

                Phil



                Code (this worked perfectly):
                function decrypt as c (e as p)
                Dim cn as sql::connection
                Dim args as sql::arguments
                Dim sqlCommand as p
                dim DOBdecrypt as c
                dim enc_str2 as c
                dim IdNum as n
                dim dataArray[0] as p
                ' Get the Record Numbers and encrypted DOB

                sqlCommand.getData = &lt;&lt;%txt%
                select idnum,dob
                from clients
                %txt%

                ' When I started getting errors, I made the change below. It's just a different format, so I'm not sure it matters. I was just trying to work with a format I was comfortable with.
                ' Obviously, once it worked, I didn't change it back to see if it mattered:

                sqlCommand.getData = "SELECT IdNum, dob FROM Clients WHERE dob &lt;&gt;' ';"
                ' I only added the "WHERE" clause because of Gregg's concern about RAM... I figured since "dob" wasn't required, I could shrink the decryption process, by eliminating
                ' records where dob was blank


                sqlCommand.writeData = &lt;&lt;%txt%
                update clients
                set clients.DOB2 = :newDOB
                where id = :IDNUM
                %txt%


                ' Again, I'm not sure that it mattered, but I changed the format of the above AND moved my revised code down, a bit,
                ' You'll find my expression after the "args/set" commands below


                flag = cn.open("::Name::localClients")
                flag = cn.topropertyarray(sqlCommand, args,dataArray)
                ' as it turns out, I think this is what the "data array" errors were caused and where my change was critical.
                ' there were 2 problems with the command, above:
                ' 1) The command was missing which sqlCommand I should run (e.g. "getdata"); and
                ' 2) The "getData" command doesn't have any arguments, so that should have been eliminated;
                'So, this is what I changed it to:

                flag = cn.topropertyarray(sqlCommand.getdata, dataArray)

                for each record in dataArray
                record.dob2 = a5_decrypt_string(record.dob,"mykey")

                args.set("IdNum",record.idnum)
                args.set("NewDOB",record.dob2)

                sqlCommand.writeData = "UPDATE Clients SET Clients.dob2 = :newDOB WHERE Id = :IdNum;"

                flag = cn.execute(sqlCommand.writeData,args)
                ' For THIS command, the "args" are required
                next 'each record

                ' Update the SQL Table record by adding the decrypted data into a field I created called DOB2

                cn.close()
                end function

                Comment


                  #9
                  Originally posted by madtowng View Post
                  ok. I didn't think a5encrypt/a5decrypt could be used directly in sql.
                  They cannot but SQL has its own built in equivalent functions:

                  decryptbypassphrase()
                  encryptbypassphrase()
                  Glen Schild



                  My Blog

                  Comment


                    #10
                    Hi Phil,
                    Glad to hear you got things done even though for some reason I can't find the message here.
                    Be nice, post the code you chose to use. You'd be surprised at how that comes in useful for people
                    looking to solve the same problem in the future.
                    Gregg
                    https://paiza.io is a great site to test and share sql code

                    Comment


                      #11
                      Gregg- I did post the code, but for some reason, they considered my response as "spam" and it's waiting for someone's approval to release it. I'm going to copy/paste my response from yesterday and see if it goes through, this time.

                      Comment


                        #12
                        First and foremost, THANK YOU Charles, Glen and Gregg! It was so kind of you to take time to help me through this. The Alpha community is go generous to give time to other developers. I hope you know how much we appreciate it.

                        I decrypted the fields, perfectly (actually, there were 2 fields I needed... I used only 1 in my example to simplify the request). Because it seemed like it would be faster and easier, I tried Glen's command in a SQL Server view. I didn't get any errors, but it didn't decrypt the field. I suspect the "a5decrypt" function uses a different methodology than SQL Server's "decryptbyphrase" function. It's also possible that I formatted the command incorrectly, but it didn't work for me, so I tried the code that Gregg shared (THANK YOU, again, for doing that. I haven't had experience with the "loop" function that you and Charles suggested, but I can see the power of that array, function, now).

                        When I added the code and ran the function, I got some Alpha errors. But, once I dissected the code and made a couple of changes, it worked perfectly. I know Gregg was concerned about the RAM and whether all of the changes could be handled at once, but it was perfect. As it turned out, there were 4000+ records to go through, and it decrypted the entire file, in less than 30 seconds (I did NOT do this on a live server... I used my Developer Edition on my computer (i7 with 16GB RAM).

                        In case someone else can use it, I've posted the revised code, below (with the revisions highlighted and comments, where needed).

                        I could not have done this without you... so, thanks again. This saved me hours of work, if I had to decrypt 4,000+ records, one-at-a-time (actually, it saved my wife the time... I was going to assign the "button pushing" to her ).

                        Phil



                        Code (this worked perfectly):
                        function decrypt as c (e as p)
                        Dim cn as sql::connection
                        Dim args as sql::arguments
                        Dim sqlCommand as p
                        dim DOBdecrypt as c
                        dim enc_str2 as c
                        dim IdNum as n
                        dim dataArray[0] as p
                        ' Get the Record Numbers and encrypted DOB

                        sqlCommand.getData = &lt;&lt;%txt%
                        select idnum,dob
                        from clients
                        %txt%

                        ' When I started getting errors, I made the change below. It's just a different format, so I'm not sure it matters. I was just trying to work with a format I was comfortable with.
                        ' Obviously, once it worked, I didn't change it back to see if it mattered:

                        sqlCommand.getData = "SELECT IdNum, dob FROM Clients WHERE dob &lt;&gt;' ';"
                        ' I only added the "WHERE" clause because of Gregg's concern about RAM... I figured since "dob" wasn't required, I could shrink the decryption process, by eliminating
                        ' records where dob was blank


                        sqlCommand.writeData = &lt;&lt;%txt%
                        update clients
                        set clients.DOB2 = :newDOB
                        where id = :IDNUM
                        %txt%


                        ' Again, I'm not sure that it mattered, but I changed the format of the above AND moved my revised code down, a bit,
                        ' You'll find my expression after the "args/set" commands below


                        flag = cn.open("::Name::localClients")
                        flag = cn.topropertyarray(sqlCommand, args,dataArray)
                        ' as it turns out, I think this is what the "data array" errors were caused and where my change was critical.
                        ' there were 2 problems with the command, above:
                        ' 1) The command was missing which sqlCommand I should run (e.g. "getdata"); and
                        ' 2) The "getData" command doesn't have any arguments, so that should have been eliminated;
                        'So, this is what I changed it to:

                        flag = cn.topropertyarray(sqlCommand.getdata, dataArray)

                        for each record in dataArray
                        record.dob2 = a5_decrypt_string(record.dob,"mykey")

                        args.set("IdNum",record.idnum)
                        args.set("NewDOB",record.dob2)

                        sqlCommand.writeData = "UPDATE Clients SET Clients.dob2 = :newDOB WHERE Id = :IdNum;"

                        flag = cn.execute(sqlCommand.writeData,args)
                        ' For THIS command, the "args" are required
                        next 'each record

                        ' Update the SQL Table record by adding the decrypted data into a field I created called DOB2

                        cn.close()
                        end function

                        Comment


                          #13
                          NOTE: The command: sqlCommand.getData = "SELECT IdNum, dob FROM Clients WHERE dob &lt;&gt;' ';" go changed when it was published... it should be:

                          sqlCommand.getData = "SELECT IdNum, dob FROM Clients WHERE dob <> ' ';"

                          Comment


                            #14
                            Glad you sorted it.

                            For information, the sql function will only decrypt if it was first encrypted in sql.
                            Glen Schild



                            My Blog

                            Comment


                              #15
                              Glad to see they're not considering you spam anymore.
                              Sorry about the hassle with sqlCommand.getData, it must be something sql server doesn't like (I prefer mysql by default, but will work with sql server when necessary).
                              Last but not least, the difference between a5_encrypt/decrypt and the encrypt/decrypt bypassphrase is where and when it's done.
                              The sql server bypassphrase does the work on the back end, where a5_encrypt/decrypt does the work on the front end (in the alpha interface).
                              Gregg
                              https://paiza.io is a great site to test and share sql code

                              Comment

                              Working...
                              X