Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
DB_escape_string
03-20-2014, 10:29 PM,
#34
RE: DB_escape_string
Hi Serakfalcon,

Thanks for the info on the audit trail functionality.

In my prior code I had not provided the supporting functions - here it is in full again with the bindResults(mysqli_stmt $stmt) and refValues($params) functions used by DB_bindquery that I hadn't previously posted.

This is fairly well tried function and based on existing open source code. It has support for php < php 5.3 and also php >= php 5.3 and it seems clean with the refValues function separated out.

The one thing missing for webERP was the corrected/updated code for the audit trail which would need to take the place of the audit trail code in the function below. If left as is we'd only get ? in the sql query in the audit trail. I've not had time this week to look at the audit trail issue so thanks Serakfalcon for your code.

In creating the DB_bindquery I tried to keep the signature as close as possible to the existing DB_query function and thus one reason why it differs from yours.

Personally I prefer to keep the $Conn in the signature for consistency and possible future use, and reduce reliance on globals as much as possible (and $_SESSION vars too but there is a big reliance on them in webERP at the moment). If we don't need the $db global i'd get rid of it.


Here below is the full code including the main DB_bindquery that I previously posted but without the audit trail functionality.

Also the example of insert/edit customer contact below for anyone that want's to try to update existing functions.

Happy to help update the codebase with whatever code is decided on.

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

    global $debug;
    global $PathPref;
    global $db;

    $query = filter_var($query, FILTER_SANITIZE_STRING); //sanitize the string
    $stmt = $Conn->prepare($query);

    if (is_array($bindvars) === true) {
        $params = array(''); // Create the empty 0 index
        foreach ($bindvars as $prop => $val) {

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

                case 'integer':
                    $params[0] .= 'i';
                    break;

                case 'blob':
                    $params[0] .= 'b';
                    break;

                case 'double':
                    $params[0] .= 'd';
                    break;
            }

            array_push($params, $bindvars[$prop]);
        }
        call_user_func_array(array($stmt, 'bind_param'), refValues($params));
    }

    $stmt->execute();
    $result = bindResults($stmt);
;
    if (DB_error_no($Conn) != 0 AND $TrapErrors==true){
        if ($TrapErrors){
            require_once($PathPrefix . 'includes/header.inc');
        }
        prnMsg($ErrorMessage . '<br />' . DB_error_msg($Conn),'error', _('Database Error'). ' ' .DB_error_no($Conn));
        if ($debug==1){
            prnMsg($DebugMessage. '<br />' . $query . '<br />','error',_('Database SQL Failure'));
        }
        if ($Transaction){
            $query = 'rollback';
            $Result = DB_query($query,$Conn);
            if (DB_error_no($Conn) !=0){
                prnMsg(_('Error Rolling Back Transaction'), 'error', _('Database Rollback Error'). ' ' .DB_error_no($Conn) );
            }else{
                prnMsg(_('Rolling Back Transaction OK'), 'error', _('Database Rollback Due to Error Above'));
            }
        }
        if ($TrapErrors){
            include($PathPrefix . 'includes/footer.inc');
            exit;
        }
    } elseif (isset($_SESSION['MonthsAuditTrail']) and (DB_error_no($Conn)==0 AND $_SESSION['MonthsAuditTrail']>0) AND ($stmt->affected_rows>0)){
        $SQLArray = explode(' ', $query);
        if (($SQLArray[0] == 'INSERT')
            OR ($SQLArray[0] == 'UPDATE')
            OR ($SQLArray[0] == 'DELETE')) {

            if ($SQLArray[2]!='audittrail'){ // to ensure the auto delete of audit trail history is not logged
                $AuditSQL = "INSERT INTO audittrail (transactiondate,
                                    userid,
                                    querystring)
                        VALUES('" . Date('Y-m-d H:i:s') . "',
                            '" . trim($_SESSION['UserID']) . "',
                            '" . $query . "')";

                $AuditResult = mysqli_query($Conn, $AuditSQL);
            }
        }
    }
    $stmt->close();

    return $result;

}

function refValues($arr)
{
    //Reference is required for PHP 5.3+
    if (strnatcmp(phpversion(), '5.3') >= 0) {
        $refs = array();
        foreach ($arr as $key => $value) {
            $refs[$key] = & $arr[$key];
        }
        return $refs;
    }
    return $arr;
}

function bindResults(mysqli_stmt $stmt)
{
    $parameters = array();
    $results = array();

    $meta = $stmt->result_metadata();

    // if $meta is false yet sqlstate is true, there's no sql error but the query is
    // most likely an update/insert/delete which doesn't produce any results
    if(!$meta && $stmt->sqlstate) {
        return array();
    }

    $row = array();
    while ($field = $meta->fetch_field()) {
        $row[$field->name] = null;
        $parameters[] = & $row[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $parameters);

    while ($stmt->fetch()) {
        $x = array();
        foreach ($row as $key => $val) {
            $x[$key] = $val;
        }
        array_push($results, $x);
    }
    return $results;
}

Update/Insert customer contact code - ideally with typed bindvars if any doubt about type:
Code:
if (isset($Id) AND ($Id AND $InputError !=1)) {
        $bindvars = array(  ($_POST['ContactName'],
                        $_POST['ContactRole'],
                        $_POST['ContactPhone'],
                        $_POST['ContactNotes'],
                        $_POST['ContactEmail'],
                        $DebtorNo,
                        (int)$Id
                        );
        $sql = "UPDATE custcontacts SET contactname= ?,
                                  role= ?,
                                  phoneno=?,
                                  notes= ?,
                                 email= ?
                    WHERE debtorno =?
                    AND contid=?";
        $msg = _('Customer Contacts') . ' ' . $DebtorNo  . ' ' . _('has been updated');
    } elseif ($InputError !=1) {
        $bindvars= array($DebtorNo,
                        $_POST['ContactName'],
                        $_POST['ContactRole'],
                        $_POST['ContactPhone'],
                        $_POST['ContactNotes'],
                        $_POST['ContactEmail']
                        );
        $sql = "INSERT INTO custcontacts (debtorno,
                                        contactname,
                                        role,
                                        phoneno,
                                        notes,
                                        email)
                VALUES (?,?,?,?,?,?)";
        $msg = _('The contact record has been added');
    }

    if ($InputError !=1) {
        $result = DB_bindquery($sql,$db,$bindvars);
                //echo '<br />' . $sql;

        echo '<br />';
        prnMsg($msg, 'success');
        echo '<br />';
        unset($Id);
        unset($_POST['ContactName']);
        unset($_POST['ContactRole']);
        unset($_POST['ContactPhone']);
        unset($_POST['ContactNotes']);
        unset($_POST['ContactEmail']);
        unset($_POST['Con_ID']);
    }
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)