Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
DB_escape_string
03-20-2014, 08:31 PM, (This post was last modified: 03-20-2014, 08:40 PM by serakfalcon.)
#33
RE: DB_escape_string
OK I have made a function that will provide this functionality. It can be used alongside the existing code, so while we should update everything to use the new code, all the existing code will still work in the meantime.
It requires a minor DB change:
Code:
ALTER TABLE `audittrail`
CHANGE COLUMN `transactiondate` `transactiondate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
ADD COLUMN `params` TEXT NULL AFTER `querystring`;

If we're adding a column I figured we might as well use TIMESTAMP, since it will accept datetimes (so the old code will work) but new code will not have to pass a date value, let SQL do the work.
(On another note, pretty much every DB has a way to log every SQL transaction, so the DB itself could do logging)

Other changes: since $db is the global connection variable, it isn't passed to the function instead the function uses the global $db.
Also, since the prepared statement must be closed inside the function, the function returns an array of rows, with each row being an associative array with the keys being the column name. So for example with the SQL
Code:
SELECT transactiondate, userid, querystring
FROM audittrail LIMIT 5
if we took $result[0]['userid'] we'd get the userid of the first result from this query.

Code:
function DB_bindquery($query,
                    $bindvars = null,
                    $ErrorMessage='',
                    $DebugMessage= null,
                    $Transaction=false,
                    $TrapErrors=true) {

    global $db;
    global $debug;
    global $PathPref;
    
    if (!isset($DebugMessage)) {
        $DebugMessage = _('The SQL that failed was');
    }
    
        //sanitize the string
    $query = filter_var($query, FILTER_SANITIZE_STRING);
        // prepare for bound query
    if (!($stmt = $db->prepare($query)) && $TrapErrors) {
        require_once($PathPrefix . 'includes/header.inc');
        prnMsg($ErrorMessage . '<br />' . $db->error,'error', _('Syntax Error'). ' ' .$db->errno);
        if ($debug==1){
            prnMsg($DebugMessage. '<br />' . $query . '<br />','error',_('Prepare Statement Failed'));
        }
        include($PathPrefix . 'includes/footer.inc');
        exit;
        
    }
    
    //prepare variables if variables are passed
    if (is_array($bindvars) === true) {
        $params = array(''); // Create the empty 0 index
        foreach ($bindvars as $val) {

            switch (gettype($val)) {
                case 'NULL':
                case 'string':
                    $params[0] .= 's';
                    break;

                case 'integer':
                case 'boolean':
                    $params[0] .= 'i';
                    break;
                    
                case 'object':
                case 'array':
                    //are these ever saved into the db?
                    $params[0] .= 'b';
                    $val = json_encode($val);
                    break;
                    
                case 'double':
                    $params[0] .= 'd';
                    break;
            }

            $params[] = $val;
        }
        $method = new ReflectionMethod('mysqli_stmt','bind_param');
        if (!$method->invokeArgs($stmt,$params) && $TrapErrors) {
            //could not bind parameters
            require_once($PathPrefix . 'includes/header.inc');
            prnMsg($ErrorMessage . '<br />' . $db->error,'error', _('Syntax Error'). ' ' .$db->errno);
            if ($debug==1){
                prnMsg($DebugMessage. '<br />' . $query . '<br />' . serialize($params) . '<br />','error',_('Invalid Parameters Passed, bind_param failed.'));
            }
            include($PathPrefix . 'includes/footer.inc');
            exit;
        }
    }
    
    $queryworked = $stmt->execute();
    $_SESSION['LastInsertId'] = $db->insert_id;
    if (!$queryworked && $TrapErrors) {
    
        require_once($PathPrefix . 'includes/header.inc');
        prnMsg($ErrorMessage . '<br />' . $db->error,'error', _('Database Error'). ' ' .$db->errno);
        if ($debug==1){
            prnMsg($DebugMessage. '<br />' . $SQL . '<br />','error',_('Database SQL Failure'));
        }
        if ($Transaction){
            $db->rollback();
            if ($db->errno !=0){
                prnMsg(_('Error Rolling Back Transaction'), 'error', _('Database Rollback Error'). ' ' .$db->errno );
            }else{
                prnMsg(_('Rolling Back Transaction OK'), 'error', _('Database Rollback Due to Error Above'));
            }
        }
        include($PathPrefix . 'includes/footer.inc');
        exit;
    } elseif (isset($_SESSION['MonthsAuditTrail']) && ($db->errno==0 && $_SESSION['MonthsAuditTrail']>0) && ($db->affected_rows)>0){

        $SQLArray = explode($query,' ');
        /*db info works on insert, update, alter table or load data infile, otherwise look for a delete
          if the third element is audittrail, don't log this as it is either DELETE FROM audittrail or INSERT INTO audittrail.  */
        if (($db->info != '' || (strpos($query,'DELETE ') !== false)) && $SQLArray[2] != 'audittrail') {
                $AuditSQL = "INSERT INTO audittrail (userid,
                                    querystring,
                                    params)
                        VALUES('" . trim($_SESSION['UserID']) . "',
                            '" . DB_escape_string($query)  . "',
                            '" . json_encode($params) . "')";

                $AuditResult = $db->query($AuditSQL);
        }
    }
    
    $meta = $stmt->result_metadata();
    //if the SQL statement returns a result set, get the result set, otherwise, return true just like mysqli_query
    if ($meta) {
        //build an associative array of rows with the appropriate column name as keys
        while ($field = $meta->fetch_field())
        {
            $resultset[] = &$row[$field->name];
        }
        $bindResults = new ReflectionMethod('mysqli_stmt','bind_result');
        $bindResults->invokeArgs($stmt,$resultset);
        $result = array();
        //the values for each column have been filled in by bind_result
        while ($stmt->fetch()) {
            foreach($row as $column => $value)
            {
                $c[$column] = $value;
            }
            $result[] = $c;
        }
    } else {
        $result = true;
    }
    $stmt->close();
    
    return $result;
}

If we're planning on rewriting all this code, we should also consider adding support for table prefixes, which would be much appreciated.
Oh, I also realized: we when parameters are passed, it should be in an associative array with named keys, this'll make PDO easier to implement later.
Reply


Messages In This Thread
DB_escape_string - by icedlava - 03-06-2014, 05:42 PM
RE: DB_escape_string - by serakfalcon - 03-06-2014, 06:26 PM
RE: DB_escape_string - by Forums - 03-06-2014, 06:26 PM
RE: DB_escape_string - by icedlava - 03-09-2014, 10:19 PM
RE: DB_escape_string - by Forums - 03-10-2014, 04:44 AM
RE: DB_escape_string - by icedlava - 03-10-2014, 10:31 AM
RE: DB_escape_string - by serakfalcon - 03-10-2014, 04:16 PM
RE: DB_escape_string - by icedlava - 03-10-2014, 04:38 PM
RE: DB_escape_string - by serakfalcon - 03-06-2014, 06:27 PM
RE: DB_escape_string - by Forums - 03-10-2014, 06:56 PM
RE: DB_escape_string - by icedlava - 03-10-2014, 07:13 PM
RE: DB_escape_string - by Forums - 03-10-2014, 10:52 PM
RE: DB_escape_string - by Forums - 03-11-2014, 05:03 AM
RE: DB_escape_string - by serakfalcon - 03-11-2014, 01:26 PM
RE: DB_escape_string - by Forums - 03-11-2014, 05:43 PM
RE: DB_escape_string - by weberp - 03-14-2014, 08:25 PM
RE: DB_escape_string - by icedlava - 03-14-2014, 08:33 PM
RE: DB_escape_string - by weberp - 03-14-2014, 09:02 PM
RE: DB_escape_string - by phil - 03-12-2014, 02:59 PM
RE: DB_escape_string - by icedlava - 03-12-2014, 03:39 PM
RE: DB_escape_string - by Forums - 03-12-2014, 08:20 PM
RE: DB_escape_string - by serakfalcon - 03-12-2014, 09:01 PM
RE: DB_escape_string - by icedlava - 03-12-2014, 09:08 PM
RE: DB_escape_string - by serakfalcon - 03-13-2014, 12:39 AM
RE: DB_escape_string - by icedlava - 03-13-2014, 11:55 AM
RE: DB_escape_string - by phil - 03-13-2014, 12:49 PM
RE: DB_escape_string - by icedlava - 03-13-2014, 01:03 PM
RE: DB_escape_string - by phil - 03-13-2014, 05:27 PM
RE: DB_escape_string - by icedlava - 03-13-2014, 05:52 PM
RE: DB_escape_string - by phil - 03-13-2014, 06:01 PM
RE: DB_escape_string - by icedlava - 03-13-2014, 06:39 PM
RE: DB_escape_string - by weberp - 03-16-2014, 03:19 AM
RE: DB_escape_string - by serakfalcon - 03-20-2014, 08:31 PM
RE: DB_escape_string - by weberp1 - 03-21-2014, 12:04 AM
RE: DB_escape_string - by serakfalcon - 03-21-2014, 01:50 AM
RE: DB_escape_string - by icedlava - 03-20-2014, 10:29 PM
RE: DB_escape_string - by serakfalcon - 03-20-2014, 11:48 PM
RE: DB_escape_string - by icedlava - 03-21-2014, 11:04 AM
RE: DB_escape_string - by icedlava - 03-21-2014, 01:08 PM
RE: DB_escape_string - by serakfalcon - 03-21-2014, 04:51 PM
RE: DB_escape_string - by icedlava - 03-21-2014, 05:48 PM
RE: DB_escape_string - by serakfalcon - 03-21-2014, 06:54 PM
RE: DB_escape_string - by icedlava - 03-22-2014, 12:31 AM
RE: DB_escape_string - by serakfalcon - 03-22-2014, 12:55 AM
RE: DB_escape_string - by phil - 03-22-2014, 03:20 PM
RE: DB_escape_string - by serakfalcon - 03-22-2014, 04:28 PM
RE: DB_escape_string - by phil - 03-23-2014, 09:00 AM
RE: DB_escape_string - by Uhuru - 03-23-2014, 08:27 PM
RE: DB_escape_string - by icedlava - 03-26-2014, 03:22 AM
RE: DB_escape_string - by serakfalcon - 03-23-2014, 06:35 PM
RE: DB_escape_string - by Uhuru - 03-26-2014, 08:05 AM
RE: DB_escape_string - by icedlava - 03-26-2014, 12:51 PM
RE: DB_escape_string - by phil - 03-26-2014, 01:40 PM
RE: DB_escape_string - by icedlava - 03-26-2014, 01:57 PM
RE: DB_escape_string - by Uhuru - 03-26-2014, 05:30 PM
RE: DB_escape_string - by icedlava - 03-26-2014, 06:04 PM
RE: DB_escape_string - by serakfalcon - 03-26-2014, 06:20 PM
RE: DB_escape_string - by icedlava - 03-26-2014, 06:27 PM
RE: DB_escape_string - by Uhuru - 03-26-2014, 06:42 PM
RE: DB_escape_string - by icedlava - 03-26-2014, 06:46 PM
RE: DB_escape_string - by Uhuru - 03-26-2014, 06:34 PM
RE: DB_escape_string - by serakfalcon - 03-26-2014, 06:40 PM
RE: DB_escape_string - by icedlava - 03-26-2014, 06:41 PM
RE: DB_escape_string - by serakfalcon - 03-26-2014, 07:38 PM
RE: DB_escape_string - by icedlava - 03-26-2014, 08:01 PM
RE: DB_escape_string - by phil - 03-27-2014, 07:24 AM
RE: DB_escape_string - by icedlava - 03-27-2014, 11:56 AM
RE: DB_escape_string - by serakfalcon - 03-27-2014, 01:11 PM
RE: DB_escape_string - by icedlava - 03-27-2014, 01:39 PM
RE: DB_escape_string - by serakfalcon - 03-27-2014, 03:32 PM
RE: DB_escape_string - by icedlava - 03-27-2014, 03:38 PM
RE: DB_escape_string - by Exsonqu_Qu - 03-27-2014, 06:00 PM
RE: DB_escape_string - by Uhuru - 03-27-2014, 06:50 PM
RE: DB_escape_string - by Exsonqu_Qu - 03-28-2014, 12:26 PM
RE: DB_escape_string - by phil - 03-27-2014, 06:57 PM
RE: DB_escape_string - by icedlava - 03-27-2014, 09:06 PM
RE: DB_escape_string - by Uhuru - 03-27-2014, 09:14 PM
RE: DB_escape_string - by icedlava - 03-27-2014, 09:21 PM
RE: DB_escape_string - by Exsonqu_Qu - 03-28-2014, 12:44 PM
RE: DB_escape_string - by Uhuru - 03-27-2014, 10:48 PM
RE: DB_escape_string - by icedlava - 03-27-2014, 10:55 PM
RE: DB_escape_string - by Uhuru - 03-27-2014, 11:44 PM
RE: DB_escape_string - by icedlava - 03-27-2014, 11:55 PM
RE: DB_escape_string - by agaluski - 03-27-2014, 11:53 PM
RE: DB_escape_string - by serakfalcon - 03-28-2014, 01:31 PM
RE: DB_escape_string - by icedlava - 03-28-2014, 03:34 PM
RE: DB_escape_string - by Uhuru - 03-28-2014, 07:33 PM
RE: DB_escape_string - by phil - 03-28-2014, 08:05 PM
RE: DB_escape_string - by Uhuru - 03-31-2014, 04:27 PM
RE: DB_escape_string - by serakfalcon - 03-31-2014, 05:47 PM

Forum Jump:


Users browsing this thread: 1 Guest(s)