1 de 7
The Oracle + PHP Cookbook
How To Encrypt Data in Oracle Using PHP
by Larry Ullman
Improve the security of your Oracle-driven PHP applications by encrypting data.
Downloads for this article:
Sample code
Oracle Database 10g
Published December 2005
While the process of storing and retrieving data is at the heart of any dynamic Web site, professional applications require more thought
and effort than these simple transactions imply. A crucial but easily overlooked aspect of data storage is protecting sensitive
information—from passwords, to credit cards, to social security numbers—using encryption. Thus an understanding as to what
encryption techniques are available in Oracle (via a PHP interface), and how best to use them, is important for all Web developers.
No "magic bullet" is available for encryption, however. Rather, a number of options are available, each differing in security level,
programming complexity, and performance overhead. (Usually an inverse relationship exists between security and performance). Before
getting into the details, you should also realize that security does not reflect a binary state—"secure" or "not secure"—but rather a
spectrum along which each application should be placed as the needs and context demand.
In this HowTo, you'll get a brief introduction to the necessary tools, and even get some specific code, for improving the security of your
data.
Background
First, let's cover basic terms and concepts. Encryption takes input, runs it through an algorithm using a specific key, and returns the
encrypted output. The complexity of the algorithm and the key dictate how secure the specific encryption is. You'll usually hear
encryption described in terms of bits: 128-bit, 256-bit, and so on. In simple terms, the more bits involved, the more secure the algorithm.
The two most basic encryption methods are hashing and Message Authentication Code (MAC, pronounced "mack"). Both techniques do
not encrypt or store the data itself but rather store the mathematically-calculated representation of the data. Here's how they work:
In hashing, when a user registers for access to a site, they create their access password; the hash of that password is stored in
the database. When the user comes back to login, a hash of the password they use at that time is compared against the stored
hash. If the two hashes match, the login and registration passwords are therefore identical. With Oracle, the two most common
hashing algorithms you would use are MD5 (128-bit ) and SHA-1 (160-bit ).
MAC involves the use of hashing along with a key--making the process more customized and therefore more secure.
With respect to database security, storing the hash or MAC value of data is effective and efficient, but only useful if you do not need to
store the data itself. Therefore, neither a hash nor a MAC can be used for credit cards, social security numbers, and the like.
On the next level of the scale is true encryption, which involves the changing of the data itself. Encryption is more complex and more of
a security risk than hashing (because you are storing a form of the original data). There are many different encryption algorithms; Digital
Encryption Standard (DES), one of the first of them and only 64-bit, is comparatively easy to hack. A few years ago DES was improved
and re-released as Triple DES, also called DES3. The latest and greatest method is Advanced Encryption Standard (AES).
Like MAC, true encryption uses a key, which is just a string of characters. The key should be in the RAW data type and the number of
characters in the key must correspond to the number of bits of encryption. For example, 128-bit AES uses a 16-character key (128 bits
divided by 8 bits per character equals 16 characters). Since the exact same key must be used to encrypt the data as to decrypt it,
protecting the key is an important security concern.
As for Oracle, hashing is accomplished with the aptly-named HASH() function; a MAC is calculated with MAC(); encryption naturally
uses ENCRYPT(); and, decryption involves DECRYPT(). These are all defined in the dbms_obfuscation_toolkit (DOTK). If you
are using Oracle Database 10g or later, the dbms_obfuscation_toolkit has been replaced by the dbms_crypto package, which
has improved features, the ability to use BLOB and CLOB data types, and support for AES (which the DOTK does not support) at 128-,
192-, or 256-bit levels. This package will need to be installed and you must be connecting to Oracle as a user with EXECUTE
permissions on dbms_crypto in order to use the following example.
Encryption Example
The sample code in this HowTo is based on a very simple table, created with this SQL command:
CREATE TABLE security (
data RAW(2000)
)
The following steps will show you exactly what you need to do to store and retrieve encrypted data in this table using Oracle. The focus
is on functional encryption, so tangential concepts, such as error management and data validation, have been ignored for brevity's sake.
See the Oracle documentation for more details.
Step 1: Create Encryption/Decryption Functions Using PL/SQL
My first recommendation is to make Oracle do as much of the work as possible. For starters, I highly recommend defining functions or
stored procedures that will handle the encryption. There are two major benefits to doing so:
The PL/SQL functions hide the specifics of the security transactions in the database, so the encryption details are not visible in
2 de 7
plain-text PHP scripts
User-defined functions will be usable by that Oracle user from any PHP script
Listing 1 contains the definitions of four functions to be used by the PHP scripts here. To create them in Oracle, run the Listing 1 code
using either a PHP script or SQL*Plus (while logged in as the same user you'll use for your PHP scripts). The functions are documented
with comments and will be explained in greater detail in each subsequent step that uses said function.
Listing 1
/* Hash calculating function.
* Takes a string of data as its argument.
* Returns a hash in raw format.
*/
CREATE OR REPLACE FUNCTION return_hash(data IN VARCHAR) RETURN RAW IS
BEGIN
/* Call HASH() function, which takes data in RAW format.
* Must use STRING_TO_RAW() to convert data.
* HASH_SH1 or HASH_MD5 are constants representing types of hashes to calculate.
*/
RETURN DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH1);
END;
/
/* MAC calculating function.
* Takes a string of data as its argument.
* Returns a hash in raw format.
*/
CREATE OR REPLACE FUNCTION return_mac(data IN VARCHAR) RETURN RAW IS
BEGIN
/* Call MAC() function, which takes data in RAW format and key in RAW format.
* Must use STRING_TO_RAW() to convert data.
* HMAC_SH1 or HMAC_MD5 are constants representing types of hashes to calculate.
* Use 16-character key with 128-bit encryption.
* Function usage:
* DBMS_CRYPTO.MAC(data AS RAW, HASH TYPE, key AS RAW)
*/
return DBMS_CRYPTO.MAC(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'),
DBMS_CRYPTO.HMAC_SH1, UTL_I18N.STRING_TO_RAW ('A1B2C3D4E5F6G7H8', 'AL32UTF8'));
END;
/
/* Encryption function.
* Takes a string of data as its argument.
* Returns data in encrypted RAW format.
*/
CREATE OR REPLACE FUNCTION return_encrypted_data(data IN VARCHAR) RETURN RAW IS
/* Define variables.
* key is the encryption key.
* encryption_mode identifies encryption algorithm and bit level
*
as well as and how chaining and padding are handled.
*/
key VARCHAR(16) := 'A1B2C3D4E5F6G7H8';
encryption_mode NUMBER := DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
/* Call ENCRYPT() function, which takes data in RAW format and key in RAW format.
* Must use STRING_TO_RAW() to convert data to RAW format.
* Use 16-character key with 128-bit encryption.
* Function usage:
* DBMS_CRYPTO.ENCRYPT(data AS RAW, ENCRYPTION TYPE, key AS RAW)
*/
RETURN DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'),
encryption_mode, UTL_I18N.STRING_TO_RAW(key, 'AL32UTF8') );
END;
/
/* Decryption function.
* Takes raw, encrypted data as its argument.
* Returns unencrypted data in string format.
*/
CREATE OR REPLACE FUNCTION return_decrypted_data(data IN RAW) RETURN VARCHAR IS
/* Define variables.
3 de 7
* key is the encryption key.
* encryption_mode identifies encryption algorithm and bit level
*
as well as and how chaining and padding are handled.
*/
key VARCHAR(16) := 'A1B2C3D4E5F6G7H8';
encryption_mode NUMBER := DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
/* Call DECRYPT() function, which takes data in RAW format and key in RAW format.
* Must use STRING_TO_RAW() to convert data to raw.
* Must use CAST_TO_VARCHAR2() to convert RAW data to string.
* Use 16-character key with 128-bit encryption.
* Function usage:
* DBMS_CRYPTO.DECRYPT(data AS RAW, ENCRYPTION TYPE, key AS RAW)
*/
RETURN UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.DECRYPT
(data, encryption_mode, UTL_I18N.STRING_TO_RAW(key, 'AL32UTF8')));
END;
/
Step 2: Insert Hashed Data into the Database
When you have defined the functions that do the encrypting, storing encrypted data in Oracle from a PHP script is rather easy. Listing 2
contains a simple PHP script for inserting hashed data into the security table. The function being used is defined as:
CREATE OR REPLACE FUNCTION return_hash(data IN VARCHAR) RETURN RAW IS
BEGIN
RETURN DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH1);
END;
The function takes a string as its only argument and returns a hashed version of that string, in the RAW format. The HASH() function
itself takes two arguments: the string being hashed (in RAW format) and a constant indicating the type of hash to be calculated. The
UTL_I18N.STRING_TO_RAW() function, which is called as part of the first argument given to the HASH() function, will turn the entered
string into a RAW version and HASH_SH1 is the hash type (SHA-1). Assuming that you have defined this function, you can then use it in
a query:
INSERT INTO security (data) VALUES (return_hash('$data'))
Put this query within the context of a standard PHP script that inserts a record (Listing 2) and voila! Run this PHP script a couple of
times, using different values for $data. (The PHP script assumes that you are running PHP 5 or greater and that you've properly
established the environmental variables for the Web server.)
Listing 2
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
<title>Storing Hashed Values with PHP</title>
</head>
<body>
<h3>Storing Hashed Values</h3>
<?php # Listing 2
// Assumes that the environmental variables have been properly set.
// Data to be stored.
$data = 'This is the data.';
echo "<p>Data being handled: <b>$data</b></p>\n";
// Connect to Oracle.
// Assumes you are using PHP 5, see the PHP manual for PHP 4 examples.
$c = oci_pconnect ('scott', 'tiger', $sid) OR die
('Unable to connect to the database. Error: <pre>' . print_r(oci_error(),1) . '</pre></body></html>');
// Define the query.
$q = "INSERT INTO security (data) VALUES (return_hash('$data'))";
// Parse the query.
$s = oci_parse($c, $q);
// Execute the query.
oci_execute ($s);
// Report on the results.
$num = oci_num_rows($s);
4 de 7
if ($num > 0) {
echo '<p>The insert query worked!</p>';
} else {
echo '<p>The insert query DID NOT work!</p>';
}
// Close the connection.
oci_close($c);
// Query to confirm the results:
// SELECT data FROM security
// SELECT * FROM security WHERE data=return_hash('$data')
?>
</body>
</html>
Then run a SELECT query in SQL*Plus to see the results (Figure 1). Remember, though, that with a hash there is no way of viewing the
original data again.
Step 3: Create a MAC Version of the Data
At this point, inserting any kind of encrypted data into a table is virtually the same from a PHP perspective—just a matter of calling the
right PL/SQL function. To populate the security table with a MAC value, use the return_mac() function:
CREATE OR REPLACE FUNCTION return_mac(data IN VARCHAR) RETURN RAW IS
BEGIN
RETURN DBMS_CRYPTO.MAC(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'),
DBMS_CRYPTO.HMAC_SH1, UTL_I18N.STRING_TO_RAW ('A1B2C3D4E5F6G7H8', 'AL32UTF8'));
END;
Again, the function takes a string as its only argument and returns a hashed version of that string, in the RAW format. This time the
MAC() function does the encrypting. It takes three arguments: the string being hashed (in RAW format), the constant indicating the type
of hash to be calculated (HMAC_SH1, which means SHA-1), and a key. The UTL_I18N.STRING_TO_RAW() function must be applied
to both the data being encrypted and the key as RAW is the format du jour.
As I mentioned previously, the length of the key should match the type of encryption—for example, a 16-character key for 128-bit
encryption. The key is something that you, the programmer, would create and hard-code into the PL/SQL function, as I've demonstrated
in this HowTo. It should obviously be kept a secret.
To insert a MAC value into the table, use this query:
INSERT INTO security (data) VALUES (return_mac('$data'))
See Listing 3 for a PHP script that executes this query:
Listing 2
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
<title>Storing MAC Values with PHP</title>
</head>
<body>
<h3>Storing MAC Values</h3>
<?php
// Assumes that the environmental variables have been properly set.
// Data to be stored.
$data = 'This is the data.';
echo "<p>Data being handled: <b>$data</b></p>\n";
// Connect to Oracle.
// Assumes you are using PHP 5, see the PHP manual for PHP 4 examples.
$c = oci_pconnect ('scott', 'tiger', $sid) OR die
5 de 7
('Unable to connect to the database. Error: <pre>' . print_r(oci_error(),1) . '</pre></body></html>');
// Define the query.
$q = "INSERT INTO security (data) VALUES (return_mac('$data'))";
// Parse the query.
$s = oci_parse($c, $q);
// Execute the query.
oci_execute ($s);
// Report on the results.
$num = oci_num_rows($s);
if ($num > 0) {
echo '<p>The insert query worked!</p>';
} else {
echo '<p>The insert query DID NOT work!</p>';
}
// Close the connection.
oci_close($c);
// Query to confirm the results:
// SELECT data FROM security
// SELECT * FROM security WHERE data=return_mac('$data')
?>
</body>
</html>
Step 4: Use AES Encryption
The next thing you should practice is how to use a recoverable encryption method in a PHP script—specifically, how to store an
encrypted value that can be retrieved in an unencrypted form. The relevant user-defined function for this is return_encrypted_data:
CREATE OR REPLACE FUNCTION return_encrypted_data(data IN VARCHAR) RETURN RAW IS
key VARCHAR(16) := 'A1B2C3D4E5F6G7H8';
encryption_mode NUMBER := DBMS_CRYPTO.ENCRYPT_AES128 +
DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
RETURN DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'),
encryption_mode, UTL_I18N.STRING_TO_RAW(key, 'AL32UTF8') );
END;
This function also takes a string as its only argument and returns an encrypted version of that string, in the RAW format. The inner
workings are a bit more complex, though. Using Oracle's ENCRYPT() function requires some more elaborate syntax, so I've started by
defining two variables that store particulars that you may want to change at a later date. Looking at the ENCRYPT() function itself, note
that its first argument is the data to be encrypted—in RAW format, of course. As with the MAC() function, ENCRYPT() takes a key as its
third argument, which should be of an appropriate length for the encryption type and be in...yes, RAW format.
The second parameter here is very important, as it dictates what specific kind of encryption should be executed. You can establish this
parameter by adding up special constants as a number value (stored in the encryption_mode variable). Here, the encryption mode is
128-bit AES (ENCRYPT_AES128), using CHAIN_CBC chaining and PAD_PKCS5 padding. (For explanations of "chaining" and "padding",
read the article referenced in this HowTo's conclusion.)
As complicated as this function may appear, using it in a query is still quite simple:
INSERT INTO security (data) VALUES (return_encrypted_data('$data'))
Listing 4 contains the PHP script that uses this query. You should empty the security table (TRUNCATE TABLE security), then run this
script a few times using different data to repopulate the database.
Listing 4
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
<title>Storing Encrypted Values with PHP</title>
</head>
<body>
<h3>Storing Encrypted Values</h3>
<?php
// Assumes that the environmental variables have been properly set.
// Data to be stored.
$data = 'This is the data.';
echo "<p>Data being handled: <b>$data</b></p>\n";
// Connect to Oracle.
6 de 7
// Assumes you are using PHP 5, see the PHP manual for PHP 4 examples.
$c = oci_pconnect ('scott', 'tiger', $sid) OR die
('Unable to connect to the database. Error: <pre>' . print_r(oci_error(),1) . '</pre></body></html>');
// Define the query.
$q = "INSERT INTO security (data) VALUES (return_encrypted_data('$data'))";
// Parse the query.
$s = oci_parse($c, $q);
// Execute the query.
oci_execute ($s);
// Report on the results.
$num = oci_num_rows($s);
if ($num > 0) {
echo '<p>The insert query worked!</p>';
} else {
echo '<p>The insert query DID NOT work!</p>';
}
// Close the connection.
oci_close($c);
// Query to confirm the results:
// SELECT data FROM security
// SELECT * FROM security WHERE data=return_encrypted_data('$data')
?>
</body>
</html>
Run a SELECT query in SQL*Plus to see the results.
Step 5: Use AES Decryption
The final step is retrieving the encrypted data in an unencrypted form. The appropriate user-defined function for this is
return_decrypted_data:
CREATE OR REPLACE FUNCTION return_decrypted_data(data IN RAW) RETURN VARCHAR IS
key VARCHAR(16) := 'A1B2C3D4E5F6G7H8';
encryption_mode NUMBER := DBMS_CRYPTO.ENCRYPT_AES128 +
DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
RETURN UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.DECRYPT
(data, encryption_mode, UTL_I18N.STRING_TO_RAW(key, 'AL32UTF8')));
END;
This function behaves slightly differently in that it takes RAW data as its lone argument and returns a string. The function calls Oracle's
DECRYPT() function, which takes raw data as its first argument (the data being decrypted), the encryption mode as its second, and the
key as its third. The values for the second and third arguments must be identical to those used to encrypt the data in the first place.
Despite this reverse behavior, the function can be used in a query as easily as the others. Naturally you would use this in a SELECT
query:
SELECT return_decrypted_data(data) AS data FROM security
Listing 5 runs this query from a PHP script and uses a loop to print the retrieved records.
Listing 5
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
<title>Retrieving Encrypted Values with PHP</title>
</head>
7 de 7
<body>
<h3>Retrieving Encrypted Values</h3>
<?php
// Assumes that the environmental variables have been properly set.
// Data to be stored.
$data = 'This is the data.';
echo "<p>Data being handled: <b>$data</b></p>\n";
// Connect to Oracle.
// Assumes you are using PHP 5, see the PHP manual for PHP 4 examples.
$c = oci_pconnect ('scott', 'tiger', $sid) OR die
('Unable to connect to the database. Error: <pre>' . print_r(oci_error(),1) . '</pre></body></html>');
// Define the query.
$q = "SELECT return_decrypted_data(data) AS data FROM security";
// Parse the query.
oci_parse($c, $q);
// Execute the query.
oci_execute ($s);
// Fetch and print the returned results.
while (oci_fetch($s)) {
echo "<p>" . oci_result($s,'DATA') . "</p>\n";
}
// Close the connection.
oci_close($c);
// Query to confirm the results:
// SELECT data FROM security
// SELECT * FROM security WHERE data=return_mac('$data')
?>
</body>
</html>
Conclusion
As you can see, performing encryption using PHP and Oracle needn't be overly complex. The trick is to define PL/SQL functions that will
do the brunt of the work. Not only does this approach make the PHP coding easier, but it's also much more secure as the specific
encryption types and the keys being used are all hidden inside Oracle. You can further hide what's happening by giving your functions
nondescript names.
For more details and for information on things like chaining and padding, search for articles by Oracle ACE and security expert Arup
Nanda. His discussion of encryption in "Protect from Prying Eyes: Encryption in Oracle 10g" is fantastic. In that article, Nanda also
discusses different key management theories that you should consider, as protecting the key is the key to the security of encrypted data.
For a non-PHP look at encryption in Oracle, read up on the Transparent Data Encryption (TDE) feature in Oracle Database 10g Release
2. This technology automatically encrypts and decrypts data for you on the fly (based upon permissions), while securely storing the keys
in a wallet file on the server.
And, of course, the most important security measure when it comes to databases is to properly administer the database: Restrict
access, establish users with appropriate permissions, and so on. Oracle Database 10g has even added column-level access to go along
with the already available row-level access.
Larry Ullman is the Director of Digital Media Technology and Lead Web Developer at DMC Insights Inc., a company specializing in
information technology. Larry lives outside of Washington, D.C., and is also the author of several books on PHP, SQL, Web
development, and other computer technologies.
Send us your comments