1
SuperGeek
Using $xoopsDB for insert/update only works when XOOPS_DB_CHKREF forced to 0.
  • 2005/8/16 16:23

  • SuperGeek

  • Just popping in

  • Posts: 16

  • Since: 2005/1/12


I've been working on this FOR DAYS with no luck and I'm not happy with the results, so I'm asking for help.

I'm attempting to use $xoopsDB (as provided in class\database\mysqldatabase.php - the typical database class XOOPS uses to access mySQL) to insert a row into a table I created named XOOPS_registration, and if a duplicate would be the result of the insert report an error that I can trap so I can update the existing row of data with any changes.

When you look at the code I added to include/functions.php (see below), you'll see that I populate the variable $sql with the SQL statement used to attempt to insert data into XOOPS_registration. Pretty standard stuff. What I expect to happen when I use $result = $xoopsDB->query($sql); is for $result to contain FALSE (because the SQL failed due to the duplicate key) and $xoopsDB->error() to display "Duplicate entry 'duplicate@x.com' for key 2" and $xoopsDB->errno() to display 1062. I get those results using $result = mysql_query($sql); but not when I use $result = $xoopsDB->query($sql); When I use $result = $xoopsDB->query($sql); I get blank for $xoopsDB->error() and 0 for $xoopsDB->errno().

Using queryF (as in $xoopsDB->queryF($sql);) changes nothing.

phpMyAdmin reports: MySQL 4.0.24-log running on localhost as kcoug@localhost
phpinfo() reports:
PHP Version: 4.3.11
SERVER["SERVER_SOFTWARE"]: Apache/1.3.33 (Unix) mod_fastcgi/2.4.2 FrontPage/5.0.2.2635 mod_jk/1.2.5

Here is how my function is called in modules/reg/reg4.php:
insert_or_update_registration($_POST);

The data in $_POST comes from my form in modules/reg/reg3.php, which is set up as follows:
echo "<form action=\"reg4.php\" method=\"post\">";

Here is the code I've added to include/functions.php:

function insert_or_update_registration($data) {
    global 
$xoopsDB$xoopsConfig$xoopsLogger;
    
$sql sprintf("INSERT INTO %s (evt_code, eMail, fName, ...similar... ) 
                        VALUES (%s, %s, %s, ...similar...)"

        
$xoopsDB->prefix("registration"),
        
$xoopsDB->quoteString($data['evt_code']),
        
$xoopsDB->quoteString($data['eMail']),
        
$xoopsDB->quoteString($data['fName']),
        ...
similar...);
echo 
"<hr />";
echo 
"RESULTS:";
$result $xoopsDB->query($sql); //Yes, I've tried queryF($sql) here - doesn't help
//$result = mysql_query($sql);   //<- Results in duplicate key error-but doesn't use $xoopsDB :(
if ($result) { // With xoopsDB, I do NOT see the following:
    
echo "INSERT succeeded<br />";
    echo 
"mysql_affected_rows=".mysql_affected_rows();
}
else { 
// With xoopsDB, I DO see the following:
    
echo "INSERT failed<br />";
    echo 
"mysql_affected_rows=".mysql_affected_rows();  
                  
// With xoopsDB, mysql_affected_rows() shows "1"
}
echo 
"<hr />";
echo 
"xoopsDB->error:";
echo 
$xoopsDB->error();  // with xoopsDB, nothing shown here.  
                         // With mysql_query, I see "Duplicate entry 'duplicate@x.com' for key 2"
                         // as expected.
echo "<hr />";
echo 
"xoopsDB->errno:";
echo 
$xoopsDB->errno();  // with xoopsDB, shows "0".  
                         // With mysql_query, I see "1062", as expected.
echo "<hr />";

//    return $xoopsDB->query($sql); // Instead of most of the above, 
                                  // I just want to do this, but it doesn't work!
}


What I need to be able to do is when the duplicate key is detected ($xoopsDB->errno()=1062), I want to do an update to the existing registration data. But since I can't even get the duplicate key to be detected when using $xoopsDB I haven't coded the update part yet.

What I've found works is modified include/common.php, per https://xoops.org/modules/newbb/viewto ... t_id=66234#forumpost66234

//    if (!defined('XOOPS_XMLRPC')) {
//        define('XOOPS_DB_CHKREF', 1);
//    } else {
        
define('XOOPS_DB_CHKREF'0);
//    }


This solves my problem. Great! But now my system is open to the Cross-Site Scripting attacks. So it appears that I can either issue an insert/update to the database, or I can have a secure website, but not both.

For now I'll use the "force XOOPS_DB_CHKREF to zero" workaround. If that proves to be too much of a security risk I can switch to using mysql_query($sql) instead of $xoopsDB->query($sql) but I don't like it. It's bad style to jump between using $xoopsDB and the standard PHP mySQL interface. I might as well just use standard PHP, and if I'm going to do that then perhaps I should just scrap using XOOPS altogether!

My preference is to use XOOPS, and have it secure, so I would appreciated it if someone can provide some insight!

Thanks!

SuperGeek

2
Mithrandir
Re: Using $xoopsDB for insert/update only works when XOOPS_DB_CHKREF forced to 0.

QueryF ought to work, since that is effectively forcing a query through - even if the referer is blocked or the request is not a POST one. Don't know why it doesn't work here.
Since your request is a POST, is your HTTP REFERER being blocked by a software firewall?

Does MySQL debug give any clues as to error messages etc.?
"When you can flatten entire cities at a whim, a tendency towards quiet reflection and seeing-things-from-the-other-fellow's-point-of-view is seldom necessary."

Cusix Software

Login

Username:
Password:

Lost Password? Register now!

Who's Online

54 user(s) are online (28 user(s) are browsing Support Forums)


Members: 0


Guests: 54


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Jun 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits