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?
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
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}’,’{cardtype}’,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.
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.
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:
/**
// 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
very nice thanks for the share will come in handy!
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 ?
[QUOTE=mstopkey;13177]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:
/**
// 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[/QUOTE]