11
Antoine
IN and NOT IN on MySQL < 4.1
  • 2005/9/22 7:26

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


Hi,

Currently I am about to implement a module I have been working on. This modules has been developed using MySQL 4.1. Now, however, I have to impelement it on a system that is running an earlier version of MySQL and doesn't support subqueries.

Please, if anyone knows how to rewrite this query so it doesn't use subqueries I'd be most gratefull. I could solve the problem in code but that would at least quadruple the execution time.

SELECT NGS_IDNGS_Schaal FROM xoops_survey_normgroepschaalxoops_survey_normgroep
WHERE NRG_ID 
7
AND xoops_survey_normgroepschaal.VRL_ID xoops_survey_normgroep.VRL_ID
AND NGS_ID NOT IN 
(SELECT NGS_ID FROM xoops_survey_normgroepschaalitem WHERE NRG_ID 7ORDER BY NGS_Schaal



12
Antoine
Re: MySQL query discrepency?
  • 2005/9/19 7:16

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


Or maybe authentication is counted as a query too? Many queries need to be done on MySQL's system tables to assert rights on tables and databases and to authenticate users.



13
Antoine
Re: Changing Username to Callsign
  • 2005/9/15 14:19

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


This would have to be changed in the following file:

language/english/global.php on line 90 reading:
define('_USERNAME','Username: ');

This changes all occurences of 'Username: ' in the core system to whatever you change it to.

Hope that helps.



14
Antoine
Re: myxoops.org hacked!
  • 2005/9/12 8:31

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


Quote:

jaquita wrote:
Is it possible for somebody to upload something that *purports* to be a image but is in fact an executable script of some kind.

Yes it is. You can upload any type of file as an avatar, as long as it isn't too large and has an allowed extension. These invalid images being referenced in an IMG tag's SRC attribute however don't pose a threat. I don't see a way to get these "bogus" images in the /upload directory included in any .php file as source.

Quote:

jaquita wrote:
Looking through the logs once again, the very last GET before the POST from user.php was for "uploads/blank.gif". At that last GET the user had a SESSIONID and one IP, when the POST happened the user had the same SESSIONID with a different IP.

Not entirely sure, but I think blank.gif is used as placeholder when a person doesn't have an avatar. Only indicates to me that the valid session-holder was probably viewing the forums right before someone else hijacked his session. No easy way to determine what was wrong unless you can replicate all the database info of the posts this user was viewing at the time this incident occured.
Though noted that the last viewed page doesn't neccesarily have to be that one with the exploit on it. It could have been any page before that during the same session.



15
Antoine
Re: xoopsDB Object
  • 2005/9/7 17:30

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


I'd pretty much use it whenever you need to include a quoted variable in a query. So basically for all variables except integers.



16
Antoine
Re: myxoops.org hacked!
  • 2005/9/7 15:12

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


Theoretically: Yes. Would have to check the sanitation of all variables involved to be sure. Don't think anything as obvious as the main message body can be used to XSS though.



17
Antoine
Re: myxoops.org hacked!
  • 2005/9/7 14:47

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


It probably isn't possible to look up the user-types by session-id anymore (e.g. did the user visiting the boards have admin rights), but as far as I can see the IP change might signify an XSS attack. All the more reason to at least never allow admins to change IP during a session.



18
Antoine
Re: xoopsDB Object
  • 2005/9/7 14:32

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


I believe that $_SERVER['REMOTE_ADDR'] is sent in the header by the client's browser and is therefore 100% spoofable. See my updated reply above for the answer to your other question.

EDIT: Looked it up on php.net, $_SERVER['REMOTE_ADDR'] seems safe enough, but it still is good practice to use quoteString.



19
Antoine
Re: xoopsDB Object
  • 2005/9/7 14:21

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


Hmm, yeah sorry, that should be:
if (intval($pid) <= 0)
    die(
"Illegal parameter value (pid)");
$xoopsDB->query("UPDATE ".$xoopsDB->prefix("users_gal")." SET visit_count = visit_count + 1, visit_lastip=".$xoopsDB->quoteString($_SERVER['REMOTE_ADDR'])." WHERE pid=$pid");


Quote:

What could happen when I wouldn't use:

if (intval($pid) <= 0)
die("Illegal parameter value (pid)");

???


If you read $pid from a GET, POST or COOKIE one could set
$pid to something like (not that great on hacking, so might not exectly work like that) "1; INSERT INTO xoops_users_link (groupid, uid) VALUES (1, my_uid)" which should effectively make you an admin on the site.



20
Antoine
Re: xoopsDB Object
  • 2005/9/7 13:53

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


XoopsDB doesn't execute command queries when the calling page reads $_GET variables as a security measure.

In this case $xoopsDB->query() does allways return true so it SEEMS like the query executed fine.
If you are aware that you are running a security risk and have taken utmost care to sanitise these $_GET vars you can call $xoopsDB->queryF() to make the changes anyways.

Talking about sanitising values:
Allways try to quote non-integer values using $xoopsDB->quoteString() and make sure any integer you include doesn't contain SQL-injection instructions e.g.:

if (intval($pid) <= 0)
    die(
"Illegal parameter value (pid)");
$xoopsDB->query("UPDATE ".$xoopsDB->prefix("users_gal")." SET visit_count = visit_count + 1, visit_lastip='".$xoopsDB->quoteString($_SERVER['REMOTE_ADDR'])."' WHERE pid=$pid");




TopTop
« 1 (2) 3 4 5 ... 11 »



Login

Who's Online

68 user(s) are online (46 user(s) are browsing Support Forums)


Members: 0


Guests: 68


more...

Donat-O-Meter

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

Latest GitHub Commits