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/viewtopic.php?topic_id=15804&forum=3&post_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
