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:
le="color: #000000">
<?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#forumpost66234le="color: #000000"><?php // 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
