Announcement

Collapse
No announcement yet.

AES_ENCRYPT {my_field} onBeforeInsert

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

  • AES_ENCRYPT {my_field} onBeforeInsert

    Hello all,

    I am trying to use AES encryption on a field with event onBeforeInsert and I cannot get the syntax correct. I can encrypt with md5 all day long and AES encrypt fields onAfterInsert into another table.
    Here is what I have:
    'cardnumber' => "aes_encrypt('{cardnumber}', '$salt')";

    $salt refers to a hidden file away from webroot with the secret.

    Any suggestions?

  • #2
    I think you make a mistake here. MD5 is a php function which encrypts the value to an undecryptable value. aes_encrypt is a MySQL function to be used in an sql statement. If the {cardnumber} is not substited by SC then you need to put it as a separate string like this:

    wrong: $mysql='select * from blabla where x > {myfield}'
    correct: $mysql='select * from blabla where x > '.{myfield}

    hope this helps
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      Hello Albert,

      I have taken a different approach and wrote a MySQL procedure that should do this with the following code:

      BEGIN
      INSERT INTO credit_cards (ccard_id, emp_id, file_number, cardtype, cardnumber, expire_mo, expire_year, ccv, name, created) VALUES
      ('{ccard_id}','{emp_id}','{file_number}','{cardtyp e}',AES_ENCRYPT('{cardnumber}', 'mypassword'),'{epire_mo}','{expire_year}','{ccv}' ,'{name}','{created}');
      END

      But, I have no clue how to use SC's Form/Procedures Insert function and can find no documentation on this.

      Comment


      • #4
        You have created this as a stored procedure in MySQL? I don't know how SC would be able to substitute all {} fields. I'm not an expert regarding stored procedures but afaik the substitution is done by MySQL, not SC. MySQL has no clue...

        Have you tried / considered to do this in a SC event? You cannot use a default form as this will generate his own insert statementsh. So you need a control application for that. Add the fields and a submit button and apply this code in php in the onvalidateok. I think that would be a working approach.
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          Hello all. Just in case anyone was needed to accomplish AES Encryption-Decryption, I though I would share what we came up with that works very well.

          Form/ onAfterInsert:

          /**
          * Encrypt inserted data
          */

          // SQL statement parameters
          $salt = '/path_to_salt_file/.salt'; // Create a file on your server named .salt and put a super secret password in it with many random characters. Make sure it is readable by the web service user.
          $fp = fopen( $salt, 'r' );
          $file_contents = fread( $fp, filesize( $salt ) );
          $file_contents = rtrim($file_contents);
          fclose( $fp );
          $update_table = 'my_table'; // Table name
          $update_where = "my_data_column_record_id = '[we_used_a_variable_on_the_field_in_the_form]'"; // Where clause
          $update_fields = array( // Field list, add as many as needed
          "my_column = aes_encrypt(my_column, '$file_contents')",
          );

          // Update record
          $update_sql = 'UPDATE ' . $update_table
          . ' SET ' . implode(', ', $update_fields)
          . ' WHERE ' . $update_where;
          sc_exec_sql($update_sql);


          To decrypt:
          Form/onLoadRecord

          // Decrypt record
          $salt = '/path_to_salt_file/.salt';
          $fp = fopen( $salt, 'r' );
          $file_contents = fread( $fp, filesize( $salt ) );
          $file_contents = rtrim($file_contents);
          fclose( $fp );
          $check_sql = "SELECT aes_decrypt(my_column, '$file_contents')"
          . " FROM my_table"
          . " WHERE record_id = '" . {my_field_record_id} . "'";
          sc_lookup(rs, $check_sql);

          if (isset({rs[0][0]})) // Row found
          {
          {my_field} = {rs[0][0]};
          }
          else // No row found
          {
          {my_field} = '';
          }

          Note: For tigher security, place .salt file outside of your web root. You can even locate it on a totally separate server.

          Mark

          Comment


          • #6
            very nice thanks for the share will come in handy!

            Comment


            • #7
              Aes_encrypt

              Hello! I know this entry too old, but it was very useful to encrypt name and lastname fileds in mysql. How ever, I have encounter a problem: When I need to search same lastname in the form, it cannot be found.

              I made a control form as custom search form, and works fine to search,


              $Apellido = sc_sql_injection({Apellido});

              $sql = "SELECT
              IDPERSONA
              FROM PERSONAS
              WHERE Apellido = AES_ENCRYPT($Apellido,'password')";


              sc_lookup(rs, $sql);

              if(count({rs}) == 0)
              {

              sc_error_exit();

              }
              else

              {
              $return = {rs[0][0]};
              $_SESSION["IDRET"]=$return;
              };




              however I dont know how to recive the session parameter inmain form and positioning the match record. Any suggestion ?



              Originally posted by mstopkey View Post
              Hello all. Just in case anyone was needed to accomplish AES Encryption-Decryption, I though I would share what we came up with that works very well.

              Form/ onAfterInsert:

              /**
              * Encrypt inserted data
              */

              // SQL statement parameters
              $salt = '/path_to_salt_file/.salt'; // Create a file on your server named .salt and put a super secret password in it with many random characters. Make sure it is readable by the web service user.
              $fp = fopen( $salt, 'r' );
              $file_contents = fread( $fp, filesize( $salt ) );
              $file_contents = rtrim($file_contents);
              fclose( $fp );
              $update_table = 'my_table'; // Table name
              $update_where = "my_data_column_record_id = '[we_used_a_variable_on_the_field_in_the_form]'"; // Where clause
              $update_fields = array( // Field list, add as many as needed
              "my_column = aes_encrypt(my_column, '$file_contents')",
              );

              // Update record
              $update_sql = 'UPDATE ' . $update_table
              . ' SET ' . implode(', ', $update_fields)
              . ' WHERE ' . $update_where;
              sc_exec_sql($update_sql);


              To decrypt:
              Form/onLoadRecord

              // Decrypt record
              $salt = '/path_to_salt_file/.salt';
              $fp = fopen( $salt, 'r' );
              $file_contents = fread( $fp, filesize( $salt ) );
              $file_contents = rtrim($file_contents);
              fclose( $fp );
              $check_sql = "SELECT aes_decrypt(my_column, '$file_contents')"
              . " FROM my_table"
              . " WHERE record_id = '" . {my_field_record_id} . "'";
              sc_lookup(rs, $check_sql);

              if (isset({rs[0][0]})) // Row found
              {
              {my_field} = {rs[0][0]};
              }
              else // No row found
              {
              {my_field} = '';
              }

              Note: For tigher security, place .salt file outside of your web root. You can even locate it on a totally separate server.

              Mark

              Comment

              Working...
              X