Menu
▾
▴
[WebERP-developers] Fwd: mysqli_real_escape_string() vs prepare/execute parameterisied queries
|
From: icedlava <ice...@gm...> - 2014-03-28 03:00:02
|
Forwarding message from Phil. This was sent in response to my email
about a commit to the new branch on DB_query changes.
Phil has requested to continue discussion on list in response to email
(also below).
Message content from Phil :
-------------------------------------------------------------
Ha!! After all that, I forgot to mysqli_real_escape_string() the
parameters ... forgive my dementia :-)
The guts of my code for DB_query() follows:
function DB_query ($SQL,
+ &$Conn,
+ $ErrorMessage='',
+ $DebugMessage= '',
+ $Transaction=false,
+ $TrapErrors=true,
+ $Parameters=array()){
+
+ global $debug;
+ global $PathPrefix;
+
+ $SQLChunks = explode('?',$SQL);
+ if (count($SQLChunks)-1 != count($Parameters)){
+ prnMsg(_('The number of parameters provided with this SQL did not
equal the number of parameters expected by the SQL string'),'error');
+ if ($debug==1){
+ prnMsg($DebugMessage. '<br />' . $SQL . '<br />','error',_('Database
SQL Failure'));
+ print_r($Parameters);
+ print_r($SQLChunks);
+ }
+ include($PathPrefix . 'includes/footer.inc');
+ exit;
+ }
+ $SQLString ='';
+ $i=0;
+ foreach ($SQLChunks as $SQLChunk) {
+ if (isset($Parameters[$i])){
+ $SQLString .= $SQLChunk . " '" . mysqli_real_escape_string($Conn,
$Parameters[$i]) . "'";
+ } else {
+ $SQLString .= $SQLChunk;
+ }
+ $i++;
+ }
+ /*
+ * if (mb_strrpos($SQL,'?')==mb_strlen($SQL)){
+ $SQLChunks[]=''; //append an empty string so the count of the arrays
match
+ }
+ */
+
+ $result=mysqli_query($Conn, $SQLString);
+
.... snipped out all the guff for audit trail last insert id etc
return $result;
+
+}
The reason why I had a go at this was firstly because my understanding
of prepared statements is that there is some server compilation and
query plan cached so that multiple runs of the query with different
parameters will be quicker - this is not how we are using mysql. There
are certain limited examples of where we could use this approach
efficiently. Security must come first too.
I accept we need to escape parameters going to the DB differently to
parameters to be displayed in html... this is the nub of the problem we
are trying to solve here - whilst maintaining the protection against SQL
injection attacks. We have had secunia and every other security outfit
all over us and if there was some vulnerability then it would be public
knowledge. So what we have works - and what we are doing is quoting all
SQL parameters and mysqli_real_escape_string() all parameters - as well
as all $_POST and $_GET output to html - which we need to treat
differently.
The link you sent me
http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/12202218#12202218
Wrapping Up
If you:
* Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc)
*OR*
* Use|mysql_set_charset()|/|$mysqli->set_charset()|
*OR*
* Use the DSN charset parameter to PDO
*OR*
* Don't use GBK or BIG-5 (you only use UTF-8 / UCS-2 / Latin-1 / ASCII)
You're 100% safe.
So we use:
webERP/includes/ConnectDB_mysqli.inc:mysqli_set_charset($db, 'utf8');
and mysql version >= 5.1
Safe examples:
Because the server's expecting UTF-8...
|mysql_set_charset('GBK');
$var= mysql_real_escape_string(chr(0xbf) . chr(0x27) . " OR 1=1
/*");
$query= "SELECT * FROM test WHERE name = '$var' LIMIT 1";|
This seems to corroborate my understanding?
However, I note the preference for parameterising queries in
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
although it does say that escaping input is a valid method ... although
a bit scathing of this approach, without really explaining why.
I have not found an explanation of why the data type of the parameter is
important to the mysqli_real_escape_string
However, I have found a number of places that concur that parameterised
queries are the ultimate defence vs injection.
Using ? in a parameter is no problem - it will be escaped. If there is a
? somewhere else - other than as a parameter marker in the SQL would be
a problem but this would not be valid SQL anyway.
Why can't we have a $DBType = 'PDO';
and an
includes/DB_Connect_PDO.inc
containing the necessary abstraction functions?
I think what I did (with an important addition of
mysql_real_escape_string($Parameters[$i])) would work and be quicker and
more readable that the bindvars solution - but I will have another play
with the bindvars code you made up and see if I can get my head around
it.
Thanks for your patience with me Jo :-)
Again - please do forward to the list if you wish - personally I prefer
all webERP related discussion to be on list - and other stuff WAY OFF
list!!
Phil
Phil Daintree
Logic Works Ltd - +64 (0)275 567890
http://www.logicworks.co.nz
On 26/03/14 23:13, icedlava wrote:
> Hi Phil,
>
> Sent this to users.sf.net but it got rejected. Sending again.
>
> Forwarded message:
>
> From: icedlava <ice...@gm...>
> To: dai...@us...
> Subject: Re: [Web-erp-svn] SF.net SVN: web-erp:[6641] branches/working
> Date: Wed, 26 Mar 2014 20:09:39 +1030
>
> Hi Phil,
>
> Thanks for the work on this so far.
>
> I do have a problem with the new DB_query you committed- how does it
> prevent the SQL injection and how does it escape the data in a
> contextual way - e.g. discriminate between string and integer which
> are the main ones?
>
> As it stands it seems to make more work using parameters passed in but
> ends up just putting a query string back together again resulting in
> no advantage.
>
> Or I might be missing something?
>
> Apologies if I sound critical, just trying to work out what your
> intent is here.
>
> Cheers,
>> Jo
>>
>> On 26 Mar 2014, at 17:03, dai...@us... wrote:
>>
>> Revision: 6641
>> http://sourceforge.net/p/web-erp/reponame/6641
>> Author: daintree
>> Date: 2014-03-26 06:33:38 +0000 (Wed, 26 Mar 2014)
>> Log Message:
>> -----------
>> DB_query alternative?
>>
>> Modified Paths:
>> --------------
>> branches/working/AccountGroups.php
>> branches/working/AddCustomerContacts.php
>> branches/working/includes/ConnectDB_mysqli.inc
>> branches/working/includes/UserLogin.php
>>
>> Modified: branches/working/AccountGroups.php
>> ===================================================================
>> --- branches/working/AccountGroups.php 2014-03-25 17:13:55 UTC (rev
>> 6640)
>> +++ branches/working/AccountGroups.php 2014-03-26 06:33:38 UTC (rev
>> 6641)
>> @@ -23,9 +23,9 @@
>> do {
>> $sql = "SELECT parentgroupname
>> FROM accountgroups
>> - WHERE groupname='" . $GroupName ."'";
>> -
>> - $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
>> + WHERE groupname=?";
>> + $Parameters = array($GroupName);
>> + $result =
>> DB_query($sql,$db,$ErrMsg,$DbgMsg,'',false,true,$Parameters);
>> $myrow = DB_fetch_row($result);
>> if ($ParentGroupName == $myrow[0]){
>> return true;
>> @@ -46,10 +46,11 @@
>> $Errors = array();
>>
>> if (isset($_POST['MoveGroup'])) {
>> - $sql="UPDATE chartmaster SET group_='" .
>> $_POST['DestinyAccountGroup'] . "' WHERE group_='" .
>> $_POST['OriginalAccountGroup'] . "'";
>> + $sql="UPDATE chartmaster SET group_=? WHERE group_=?";
>> + $Parameters = array($_POST['DestinyAccountGroup'],
>> $_POST['OriginalAccountGroup']);
>> $ErrMsg = _('An error occurred in moving the account group');
>> $DbgMsg = _('The SQL that was used to move the account group was');
>> - $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
>> + $result = DB_query($sql,$db,$ErrMsg,$DbgMsg,false,true,
>> $Parameters);
>> echo '<div class="centre"><a href="' .
>> htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">' .
>> _('Review Account Groups') . '</a></div>';
>> prnMsg( _('All accounts in the account group:') . ' ' .
>> $_POST['OriginalAccountGroup'] . ' ' . _('have been changed to the
>> account group:') . ' ' . $_POST['DestinyAccountGroup'],'success');
>> }
>> @@ -68,12 +69,12 @@
>>
>> $sql="SELECT count(groupname)
>> FROM accountgroups
>> - WHERE groupname='" . $_POST['GroupName'] . "'";
>> -
>> + WHERE groupname=? ";
>> + $Parameters=array($_POST['GroupName']);
>> $DbgMsg = _('The SQL that was used to retrieve the information
>> was');
>> $ErrMsg = _('Could not check whether the group exists because');
>>
>> - $result=DB_query($sql, $db,$ErrMsg,$DbgMsg);
>> + $result=DB_query($sql, $db,$ErrMsg,$DbgMsg,false,true,$Parameters);
>> $myrow=DB_fetch_row($result);
>>
>> if ($myrow[0] != 0 AND $_POST['SelectedAccountGroup'] == '') {
>> @@ -105,12 +106,12 @@
>> sequenceintb,
>> sectioninaccounts
>> FROM accountgroups
>> - WHERE groupname='" . $_POST['ParentGroupName'] . "'";
>> -
>> + WHERE groupname=?";
>> + $Parameters = array($_POST['ParentGroupName']);
>> $DbgMsg = _('The SQL that was used to retrieve the information
>> was');
>> $ErrMsg = _('Could not check whether the group is recursive
>> because');
>>
>> - $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
>> + $result =
>> DB_query($sql,$db,$ErrMsg,$DbgMsg,false,true,$Parameters);
>>
>> $ParentGroupRow = DB_fetch_array($result);
>> $_POST['SequenceInTB'] = $ParentGroupRow['sequenceintb'];
>> @@ -147,31 +148,38 @@
>> DB_IgnoreForeignKeys($db);
>>
>> $sql = "UPDATE chartmaster
>> - SET group_='" . $_POST['GroupName'] . "'
>> - WHERE group_='" . $_POST['SelectedAccountGroup'] . "'";
>> + SET group_=?
>> + WHERE group_=?";
>> + $Parameters = array($_POST['GroupName'],
>> + $_POST['SelectedAccountGroup']);
>> $ErrMsg = _('An error occurred in renaming the account group');
>> $DbgMsg = _('The SQL that was used to rename the account group
>> was');
>>
>> - $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
>> + $result = DB_query($sql, $db, $ErrMsg,
>> $DbgMsg,false,true,$Parameters);
>>
>> $sql = "UPDATE accountgroups
>> - SET parentgroupname='" . $_POST['GroupName'] . "'
>> - WHERE parentgroupname='" . $_POST['SelectedAccountGroup'] .
>> "'";
>> + SET parentgroupname=?
>> + WHERE parentgroupname=?";
>>
>> - $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
>> + $result = DB_query($sql, $db, $ErrMsg,
>> $DbgMsg,false,true,$Parameters);
>>
>> DB_ReinstateForeignKeys($db);
>> }
>>
>> - $sql = "UPDATE accountgroups SET groupname='" .
>> $_POST['GroupName'] . "',
>> - sectioninaccounts='" . $_POST['SectionInAccounts'] . "',
>> - pandl='" . $_POST['PandL'] . "',
>> - sequenceintb='" . $_POST['SequenceInTB'] . "',
>> - parentgroupname='" . $_POST['ParentGroupName'] . "'
>> - WHERE groupname = '" . $_POST['SelectedAccountGroup'] .
>> "'";
>> + $sql = "UPDATE accountgroups SET groupname=?,
>> + sectioninaccounts=?,
>> + pandl=?,
>> + sequenceintb=?,
>> + parentgroupname=?
>> + WHERE groupname=?";
>> $ErrMsg = _('An error occurred in updating the account group');
>> $DbgMsg = _('The SQL that was used to update the account group
>> was');
>> -
>> + $Parameters = array($_POST['GroupName'],
>> + $_POST['SectionInAccounts'],
>> + $_POST['PandL'],
>> + $_POST['SequenceInTB'],
>> + $_POST['ParentGroupName'],
>> + $_POST['SelectedAccountGroup']);
>> $msg = _('Record Updated');
>> } elseif ($InputError !=1) {
>>
>> @@ -182,47 +190,52 @@
>> sequenceintb,
>> pandl,
>> parentgroupname
>> - ) VALUES (
>> - '" . $_POST['GroupName'] . "',
>> - '" . $_POST['SectionInAccounts'] . "',
>> - '" . $_POST['SequenceInTB'] . "',
>> - '" . $_POST['PandL'] . "',
>> - '" . $_POST['ParentGroupName'] . "')";
>> + ) VALUES ( ?,
>> + ?,
>> + ?,
>> + ?,
>> + ?)";
>> $ErrMsg = _('An error occurred in inserting the account group');
>> $DbgMsg = _('The SQL that was used to insert the account group
>> was');
>> + $Parameters = array($_POST['GroupName'],
>> + $_POST['SectionInAccounts'],
>> + $_POST['SequenceInTB'],
>> + $_POST['PandL'],
>> + $_POST['ParentGroupName']);
>> $msg = _('Record inserted');
>> }
>>
>> if ($InputError!=1){
>> //run the SQL from either of the above possibilites
>> - $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
>> + $result = DB_query($sql, $db, $ErrMsg,
>> $DbgMsg,false,true,$Parameters);
>> prnMsg($msg,'success');
>> unset ($_POST['SelectedAccountGroup']);
>> unset ($_POST['GroupName']);
>> unset ($_POST['SequenceInTB']);
>> }
>> -} elseif (isset($_GET['delete'])) {
>> +} elseif (isset($_GET['Delete'])) {
>> //the link to delete a selected record was clicked instead of the
>> submit button
>>
>> // PREVENT DELETES IF DEPENDENT RECORDS IN 'ChartMaster'
>>
>> - $sql= "SELECT COUNT(group_) AS groups FROM chartmaster WHERE
>> chartmaster.group_='" . $_GET['SelectedAccountGroup'] . "'";
>> + $sql= "SELECT COUNT(group_) AS groups FROM chartmaster WHERE
>> chartmaster.group_=?";
>> $ErrMsg = _('An error occurred in retrieving the group information
>> from chartmaster');
>> $DbgMsg = _('The SQL that was used to retrieve the information
>> was');
>> - $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
>> + $Parameters = array($_GET['SelectedAccountGroup']);
>> +
>> + $result = DB_query($sql, $db, $ErrMsg,
>> $DbgMsg,false,true,$Parameters);
>> $myrow = DB_fetch_array($result);
>> if ($myrow['groups']>0) {
>> prnMsg( _('Cannot delete this account group because general ledger
>> accounts have been created using this group'),'warn');
>> echo '<br />' . _('There are') . ' ' . $myrow['groups'] . ' ' .
>> _('general ledger accounts that refer to this account group');
>> - echo '<br /><form method="post" id="AccountGroups" action="' .
>> htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
>> + echo '<br /><form method="post" id="AccountGroups" action="' .
>> htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">
>> + <input type="hidden" name="FormID" value="' .
>> $_SESSION['FormID'] . '" />
>> + <table class="selection">
>> + <input type="hidden" name="OriginalAccountGroup" value="' .
>> $_GET['SelectedAccountGroup'] . '" />
>> + <tr>
>> + <td>' . _('Parent Group') . ':' . '</td>
>> + <td><select tabindex="2" ' .
>> (in_array('ParentGroupName',$Errors) ? 'class="selecterror"' : '' )
>> . ' name="DestinyAccountGroup">';
>>
>> - echo '<input type="hidden" name="FormID" value="' .
>> $_SESSION['FormID'] . '" />';
>> - echo '<table class="selection">';
>> - echo '<input type="hidden" name="OriginalAccountGroup" value="' .
>> $_GET['SelectedAccountGroup'] . '" />';
>> - echo '<tr>
>> - <td>' . _('Parent Group') . ':' . '</td>
>> - <td><select tabindex="2" ' .
>> (in_array('ParentGroupName',$Errors) ? 'class="selecterror"' : '' )
>> . ' name="DestinyAccountGroup">';
>> -
>> $sql = "SELECT groupname FROM accountgroups";
>> $GroupResult = DB_query($sql, $db,$ErrMsg,$DbgMsg);
>> while ($GroupRow = DB_fetch_array($GroupResult) ) {
>> @@ -233,28 +246,30 @@
>> echo '<option value="'.htmlentities($GroupRow['groupname'],
>> ENT_QUOTES,'UTF-8').'">' .htmlentities($GroupRow['groupname'],
>> ENT_QUOTES,'UTF-8') . '</option>';
>> }
>> }
>> - echo '</select>';
>> - echo '</td></tr>';
>> - echo '<tr>
>> - <td colspan="2"><div class="centre"><input tabindex="6"
>> type="submit" name="MoveGroup" value="' . _('Move Group') . '"
>> /></div></td>
>> - </tr>
>> - </table>';
>> + echo '</select></td>
>> + </tr>
>> + <tr>
>> + <td colspan="2"><div class="centre"><input tabindex="6"
>> type="submit" name="MoveGroup" value="' . _('Move Group') . '"
>> /></div></td>
>> + </tr>
>> + </table>';
>>
>> } else {
>> - $sql = "SELECT COUNT(groupname) groupnames FROM accountgroups
>> WHERE parentgroupname = '" . $_GET['SelectedAccountGroup'] . "'";
>> + $sql = "SELECT COUNT(groupname) groupnames FROM accountgroups
>> WHERE parentgroupname=?";
>> $ErrMsg = _('An error occurred in retrieving the parent group
>> information');
>> $DbgMsg = _('The SQL that was used to retrieve the information
>> was');
>> - $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
>> + $Parameters = array($_GET['SelectedAccountGroup']);
>> + $result = DB_query($sql, $db, $ErrMsg,
>> $DbgMsg,false,true,$Parameters);
>> +
>> $myrow = DB_fetch_array($result);
>> if ($myrow['groupnames']>0) {
>> prnMsg( _('Cannot delete this account group because it is a parent
>> account group of other account group(s)'),'warn');
>> echo '<br />' . _('There are') . ' ' . $myrow['groupnames'] . ' '
>> . _('account groups that have this group as its/there parent account
>> group');
>> -
>> } else {
>> - $sql="DELETE FROM accountgroups WHERE groupname='" .
>> $_GET['SelectedAccountGroup'] . "'";
>> + $sql="DELETE FROM accountgroups WHERE groupname=?";
>> $ErrMsg = _('An error occurred in deleting the account group');
>> $DbgMsg = _('The SQL that was used to delete the account group
>> was');
>> - $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
>> + $Parameters = array($_GET['SelectedAccountGroup']);
>> + $result = DB_query($sql, $db, $ErrMsg,
>> $DbgMsg,false,true,$Parameters);
>> prnMsg( $_GET['SelectedAccountGroup'] . ' ' . _('group has been
>> deleted') . '!','success');
>> }
>>
>> @@ -323,7 +338,7 @@
>> <td>' . $PandLText . '</td>
>> <td>' . $myrow['parentgroupname'] . '</td>';
>> echo '<td><a href="' . htmlspecialchars($_SERVER['PHP_SELF'] .
>> '?SelectedAccountGroup=' . urlencode($myrow['groupname']),
>> ENT_QUOTES,'UTF-8') . '">' . _('Edit') . '</a></td>';
>> - echo '<td><a href="' . htmlspecialchars($_SERVER['PHP_SELF'] .
>> '?SelectedAccountGroup=' . urlencode($myrow['groupname']),
>> ENT_QUOTES,'UTF-8') . '&delete=1" onclick="return confirm(\'' .
>> _('Are you sure you wish to delete this account group?') . '\');">' .
>> _('Delete') . '</a></td></tr>';
>> + echo '<td><a href="' . htmlspecialchars($_SERVER['PHP_SELF'] .
>> '?SelectedAccountGroup=' . urlencode($myrow['groupname']),
>> ENT_QUOTES,'UTF-8') . '&Delete=1" onclick="return confirm(\'' .
>> _('Are you sure you wish to delete this account group?') . '\');">' .
>> _('Delete') . '</a></td></tr>';
>>
>> } //END WHILE LIST LOOP
>> echo '</table>';
>> @@ -334,7 +349,7 @@
>> echo '<div class="centre"><br /><a href="' .
>> htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">' .
>> _('Review Account Groups') . '</a></div>';
>> }
>>
>> -if (!isset($_GET['delete'])) {
>> +if (!isset($_GET['Delete'])) {
>>
>> echo '<form method="post" id="AccountGroups" action="' .
>> htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
>> echo '<div><br />';
>> @@ -349,11 +364,12 @@
>> pandl,
>> parentgroupname
>> FROM accountgroups
>> - WHERE groupname='" . $_GET['SelectedAccountGroup'] ."'";
>> + WHERE groupname=?";
>>
>> $ErrMsg = _('An error occurred in retrieving the account group
>> information');
>> $DbgMsg = _('The SQL that was used to retrieve the account group
>> and that failed in the process was');
>> - $result = DB_query($sql, $db,$ErrMsg,$DbgMsg);
>> + $Parameters = array($_GET['SelectedAccountGroup']);
>> + $result = DB_query($sql, $db, $ErrMsg,
>> $DbgMsg,false,true,$Parameters);
>> if (DB_num_rows($result) == 0) {
>> prnMsg( _('The account group name does not exist in the
>> database'),'error');
>> include('includes/footer.inc');
>> @@ -471,4 +487,4 @@
>>
>> } //end if record deleted no point displaying form to add record
>> include('includes/footer.inc');
>> -?>
>> +?>
>> \ No newline at end of file
>>
>> Modified: branches/working/AddCustomerContacts.php
>> ===================================================================
>> --- branches/working/AddCustomerContacts.php 2014-03-25 17:13:55 UTC
>> (rev 6640)
>> +++ branches/working/AddCustomerContacts.php 2014-03-26 06:33:38 UTC
>> (rev 6641)
>> @@ -56,8 +56,7 @@
>> $_POST['ContactNotes'],
>> $_POST['ContactEmail'],
>> $DebtorNo,
>> - (int)$Id
>> - );
>> + (int)$Id);
>> $sql = "UPDATE custcontacts SET contactname=?,
>> role=?,
>> phoneno=?,
>> @@ -72,8 +71,7 @@
>> $_POST['ContactRole'],
>> $_POST['ContactPhone'],
>> $_POST['ContactNotes'],
>> - $_POST['ContactEmail']
>> - );
>> + $_POST['ContactEmail']);
>> $sql = "INSERT INTO custcontacts (debtorno,
>> contactname,
>> role,
>>
>> Modified: branches/working/includes/ConnectDB_mysqli.inc
>> ===================================================================
>> --- branches/working/includes/ConnectDB_mysqli.inc 2014-03-25
>> 17:13:55 UTC (rev 6640)
>> +++ branches/working/includes/ConnectDB_mysqli.inc 2014-03-26
>> 06:33:38 UTC (rev 6641)
>> @@ -1,11 +1,6 @@
>> <?php
>> /* $Id$ */
>>
>> -/* PeterMoulding.com
>> -20071102 Change from mysql to mysqli;
>> -20071102 Add $db to DB_escape_string();
>> -*/
>> -
>> define ('LIKE','LIKE');
>>
>> if (!isset($mysqlport)){
>> @@ -16,14 +11,13 @@
>> $db = mysqli_connect($host , $DBUser,
>> $DBPassword,$_SESSION['DatabaseName'], $mysqlport);
>> //$result=DB_query('SET sql_mode = ANSI', $db);
>>
>> -
>> //this statement sets the charset to be used for sending data to and
>> from the db server
>> //if not set, both mysqli server and mysqli client/library may assume
>> otherwise
>> mysqli_set_charset($db, 'utf8');
>>
>> /* check connection */
>> if (mysqli_connect_errno()) {
>> - printf("Connect failed: %s\n", mysqli_connect_error());
>> + echo _('Connect failed') . ': ' . mysqli_connect_error();
>> session_unset();
>> session_destroy();
>> echo '<p>' . _('Click') . ' ' . '<a href="index.php">' . _('here') .
>> '</a>' . ' ' ._('to try logging in again') . '</p>';
>> @@ -70,7 +64,101 @@
>> *
>> * @return $result associative array
>> */
>> +
>> +
>> function DB_query ($SQL,
>> + &$Conn,
>> + $ErrorMessage='',
>> + $DebugMessage= '',
>> + $Transaction=false,
>> + $TrapErrors=true,
>> + $Parameters=array()){
>> +
>> + global $debug;
>> + global $PathPrefix;
>> +
>> + $SQLChunks = explode('?',$SQL);
>> + if (count($SQLChunks)-1 != count($Parameters)){
>> + prnMsg(_('The number of parameters provided with this SQL did not
>> equal the number of parameters expected by the SQL string'),'error');
>> + if ($debug==1){
>> + prnMsg($DebugMessage. '<br />' . $SQL . '<br
>> />','error',_('Database SQL Failure'));
>> + print_r($Parameters);
>> + print_r($SQLChunks);
>> + }
>> + include($PathPrefix . 'includes/footer.inc');
>> + exit;
>> + }
>> + $SQLString ='';
>> + $i=0;
>> + foreach ($SQLChunks as $SQLChunk) {
>> + if (isset($Parameters[$i])){
>> + $SQLString .= $SQLChunk . " '" . $Parameters[$i] . "'";
>> + } else {
>> + $SQLString .= $SQLChunk;
>> + }
>> + $i++;
>> + }
>> + /*
>> + * if (mb_strrpos($SQL,'?')==mb_strlen($SQL)){
>> + $SQLChunks[]=''; //append an empty string so the count of the
>> arrays match
>> + }
>> + */
>> +
>> + $result=mysqli_query($Conn, $SQLString);
>> +
>> + $_SESSION['LastInsertId'] = mysqli_insert_id($Conn);
>> +
>> + if ($DebugMessage == '') {
>> + $DebugMessage = _('The SQL that failed was');
>> + }
>> +
>> + 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 />' . $SQLString . '<br
>> />','error',_('Database SQL Failure'));
>> + }
>> + if ($Transaction){
>> + $SQL = 'rollback';
>> + $Result = DB_query($SQL,$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
>> (DB_affected_rows($result)>0)){
>> +
>> + $SQLArray = explode(' ', $SQLString);
>> +
>> + 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']) . "',
>> + '" . $SQLString . "')";
>> +
>> + $AuditResult = mysqli_query($Conn, $AuditSQL);
>> + }
>> + }
>> + }
>> + return $result;
>> +
>> +}
>> +
>> +
>> +function DB_query_JO ($SQL,
>> &$Conn,
>> $ErrorMessage='',
>> $DebugMessage= '',
>>
>> Modified: branches/working/includes/UserLogin.php
>> ===================================================================
>> --- branches/working/includes/UserLogin.php 2014-03-25 17:13:55 UTC
>> (rev 6640)
>> +++ branches/working/includes/UserLogin.php 2014-03-26 06:33:38 UTC
>> (rev 6641)
>> @@ -42,12 +42,13 @@
>> /* The SQL to get the user info must use the * syntax because the
>> field name could change between versions if the fields are specifed
>> directly then the sql fails and the db upgrade will fail */
>> $sql = "SELECT *
>> FROM www_users
>> - WHERE www_users.userid='" . $Name . "'
>> - AND (www_users.password='" . CryptPass($Password) . "'
>> - OR www_users.password='" . $Password . "')";
>> + WHERE www_users.userid=?
>> + AND (www_users.password=?
>> + OR www_users.password=?)";
>> + $Parameters = array($Name, CryptPass($Password), $Password);
>> $ErrMsg = _('Could not retrieve user details on login because');
>> $debug =1;
>> - $Auth_Result = DB_query($sql, $db,$ErrMsg);
>> + $Auth_Result = DB_query($sql,
>> $db,$ErrMsg,'',false,true,$Parameters);
>> // Populate session variables with data base results
>> if (DB_num_rows($Auth_Result) > 0) {
>> $myrow = DB_fetch_array($Auth_Result);
>>
>>
>> ------------------------------------------------------------------------------
>> Learn Graph Databases - Download FREE O'Reilly Book
>> "Graph Databases" is the definitive new guide to graph databases and
>> their
>> applications. Written by three acclaimed leaders in the field,
>> this first edition is now available. Download your free book today!
>> http://p.sf.net/sfu/13534_NeoTech
>> _______________________________________________
>> Web-erp-svn mailing list
>> Web...@li...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-svn
|
