#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    MySQL newbie here


    I'm taking old (PHP) code from the mid 2000's and trying to get it working with the latest MySQL (5.1.66-0+squeeze1) running under Debian. I am very well versed in Linux and even PHP but new(ish) to MySQL. I am using phpMyAdmin to work with the database.

    Anyway, this older code makes this call:
    Code:
    if( $port != 0 ) {
    	$query="select * from config 
    			where type like 'prog' and 
    			port = $port and 
    			command not like '*3%'
    			order by command, sub";
    } else {
    	$query="select * from config 
    			where type like 'prog' and 
    			tab = '$tab' and 
    			command not like '*3%'
    			order by command, sub";
    }

    And SQL balks as:

    ack! query failed:
    errorno=1064
    error=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by description' at line 1
    query=select * from config where type like 'prog' and command like '*3%' and port = order by description

    There are no mysql error logs to look at, nor anything is syslogs either
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    the queries of your PHP snippet are not the ones throwing the error, as you can see in the error message.

    This is the query you wanna look at:
    Code:
    select * from config where type like 'prog' and command like '*3%' and port = order by description
    After the "port =", there's no value. So you have to add something there (whatever that might be):
    Code:
    SELECT
    	...
    FROM
    	...
    WHERE
    	...
    	AND port = ???
    ORDER BY
    	description
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Yea, I see I copied from the wrong file. The following is correct:

    Code:
    $query="select * from config where type like 'prog' and command like '*3%' and port = $port order by description";
    I'm trying to figure out where $port is being assigned as that appears to be the cause of the failure.

    (this is a suite of files, generating an interactive website)
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Man o man, I don't get it.

    The exact same php files run perfectly on the original machine they came from. And the database files likewise work perfectly. But on the new machine, the error(s) as I show above are all over the place. For example, I don't see where $port is being set ANYWHERE in the code so I'm thinking it's some sort of environment variable. (there are other similar errors in other parts of the code too - the same problem that a variable isn't being seen)

    The old machine is running a very old version of both MySQL and PHP and I'm figuring that is part of the problem. I did transfer the database to the new machine and can query it just fine

    Grrrrrrr.....
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Resolved


    Duh....turned out I didn't have global variables enabled in php.ini.

    That fixed everything
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Red face


    Funny thing about learning curves. They're a lot like code development in that, just when you think you have it done, something creeps in and ruins your day!

    Well.. I got everything going on the new machine just fine and upgraded a bunch of packages, including PHP... well.. that broke the register_globals setting! So some research showed I was stupid for using them in the first place as they're a serious security risk!

    Since my suite of scripts is pretty small (only 12 or so of 'em), I simply added
    Code:
    $port = $_GET['port'];
    to each of the files and everything seems happy now.

    Whew!

    Comments on this post

    • MrFujin agrees : You managed to find the security risk... that is not stupid. ;)
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    If you still have the
    Code:
    WHERE port = $port
    then your script is wide open to SQL injections.

    Anybody can manipulate the query and fetch any critical data (like passwords, email addresses etc.) simply by passing SQL commands through the "port" parameter:
    Code:
    ''
    UNION
    SELECT
    	, user_name
    	, credit_card_number
    	, password
    	, email_address
    FROM
    	members
    Put into your query, this will display a convenient list of credit card numbers etc. instead of the config values.

    To put it bluntly: That code is very, very bad. Whoever wrote it obviously had no idea about security and didn't even care. So if you want your website to actually withstand attacks and not just somehow "run" until the first script kiddie comes around, you'll need to do much more than replace $port with $_GET['port']. It's probably a major rewrite, because all code snippets you posted have this vulnerability, and I fear the rest isn't better.

    Every value must be escaped with mysql_real_escape_string() before being put into a query strings. The same goes for values that are put into the HTML markup (with "echo", "print" etc.). Those have to be escaped with htmlentities(). There might be many other vulnerabilities like internal error messages being output and whatnot.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    I totally agree with Jacques1, but would recommend PDO to implement.

IMN logo majestic logo threadwatch logo seochat tools logo