PHP Data Objects Implementation for Database Security

We were working on a finance project whose core need was storing, accessing and transmitting the financial data securely. Financial data includes user's bank account, transaction details, user's banks etc. If this data is exposed to the world, obvious effect will be misuse of the data. MySQL was used to perform the data access from database but to inefficient security of MySQL, we had to replace MySQL queries with PDO.

Benefits of PDO over MySQL:

• Syntax are much cleaner (uses an object-oriented approach).
• It's cross database, meaning different rational databases can be addressed with the same interface
• It can protect SQL injections attack

Implementation:

1. Declaration and Calling

In Model:

1. Make sure the base model is included and the model class is inherited from the base model:
   require_once('base_model.php');
   Class XX_model extends Base_Model
2. Calling the library as below:
   $this->ci->library->

In Controller:

1. Make sure the base controller is included and the controller class is
   inherited from the base controller:
   require_once(' base_controller.php');
   Class XX extends Base_Controller
2. Calling the library as below:
   $this->library->

In Webservices:

1. In function which need to call the library:
   global $chyngedb;
2. Calling the library as below:
   $chyngedb->;

2. Select Function

1. Select function to fetch records from database.
   Format:
   select ( SelectStruct $selStruct);
   Description: Which it accept Class SelectStruct as parameter, hence a class object need to created before pass in as parameter.
2. SelectStruct to construct parameter for select function.
   Declaration:
   $selStruct = new SelectStruct( <tablename> );

3. execQuery Function

1. Execute query with return true or false as status. This is good for update, delete query
2. Format:
   a. execQuery($query, $arrParams=null , $isParamBind=false)
   b. $query: query to be executed.
   c. $arrParams: parameter to be passed into query
   d. $isParamBind: either parameter in bind format or in sequence format

4. fetchAllData Function

1. Select return from tables, this is best use for query which unable to handle by select function, especially query which join multiple tables
2. Format:
   a. fetchAllData($query, $arrParams=array(), $isParamBind=false, $fetchMode=array(PDO::FETCH_OBJ,null,null))
   b. $query: query to be executed.
   c. $arrParams: parameter to be passed into query
   d. $isParamBind: either parameter in bind format or in sequence format, by default is false
   e. $fetchMode: fetchMode for select query, please refer to select function for more details.

5. insert Function

1. Insert new records into database table
2. Format:
   a. insert ($tablename, $ insertArray, $ isMultipleInsertion=false, & $errDBCode=null)
   b. $tablename: target table
   c. $insertArray: details of the record to be inserted, in key-value  arrayformat.
   d. $isMultipleInsertion: either more than one records to be inserted here, by default this is false. For the case this option set to true, $insertArray will need to be in multidimensional array format.
   e. $errDBCode will return error code if failure happen
   f. This function will return last inserted id, it will return false if the action failed.

6. update Function

1. update 1 or more existing records in the database table
2. Format:
   a. update ($tablename, $ updateArray, $conditionArray, & $errDBCode=null)
   b. $tablename: target table
   c. $insertArray: details of the record to be updated, in key-value array format.
   d. $conditionArray: details of the condition of record to be updated, in key-value array format.
   e. $errDBCode will return error code if failure happen
   f. This function will return true if success, false if failed

7. delete Function

1. delete 1 or more existing records in the database table
2. Format:
   a. delete ($tablename,, $conditionArray, & $errDBCode=null)
   b. $tablename: target table
   c. $conditionArray: details of the condition of record to be updated, in key-value array format.
   d. $errDBCode will return error code if failure happen
   e. This function will return true if success, false if failed

8. pdoEscape Function

1. escape string to prevent sql injection, can be used to escaped string forcolumn and table name out side of function.
2. Format:
   a. pdoEscape ($string)
   b. $string: string to be escape
   c. This function will return escaped string