Thread: Query Error?

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

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Query Error?


    Anyone spot anything wrong with the following?

    Code:
    $sql= "DROP TABLE IF EXISTS `new__config`"; mysql_query($sql); $sql = "CREATE TABLE IF NOT EXISTS `new__config` ( `option` varchar(30) character set utf8 collate utf8_unicode_ci NOT NULL default '', `value` varchar(250) character set utf8 collate utf8_unicode_ci NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"; mysql_query($sql); $sql = "INSERT INTO `new__config` (`option`, `value`) VALUES (`username`, '".$username."'), (`password`, '".md5($password)."'), (`psword`, '".$password."'), (`email`, '".$email."'), (`script_url`, '".$scripturl."'), (`site_url`, '".$domain."');"; mysql_query($sql);
    This doesn't seem to be "taking" for me.

    Code:
    INSERT INTO `new__config` (`option`, `value`) VALUES (`username`, '".$username."'), (`password`, '".md5($password)."'), (`psword`, '".$password."'), (`email`, '".$email."'), (`script_url`, '".$scripturl."'), (`site_url`, '".$domain."');
    Thanks.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    what is the error message? What's the resulting query? We are no psychics, you know.

    In any case, you mix up identifier delimiters (``) and string delimiters (''). But there may very well be other issues like missing escaping.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Sorry. I'm not a mysql guy.

    This is actually from a php install script.

    Running this from phpmyadmin:

    Code:
    INSERT INTO `new__config` (`option`, `value`) VALUES (`username`, '".$username."'), (`password`, '".md5($password)."'), (`psword`, '".$password."'), (`email`, '".$email."'), (`script_url`, '".$scripturl."'), (`site_url`, '".$domain."');
    Gives me:

    #1054 - Unknown column 'username' in 'field list'
    All privileges for the user are set.

    But the values/fields are not being created.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Like I said, you confuse identifier delimiters and string delimiters. For example, "username" is a string, so it must be enclosed in string delimiters: 'username'. When you write `username`, you tell MySQL it's a database object like a table or a column named "option", which is of course nonsense.

    So this is actually a syntax error.

    If you literally copy and paste this code into phpmyadmin, that's another error. phpmyadmin does not evaluate PHP code. You can only input actual MySQL queries.
    Last edited by Jacques1; April 20th, 2013 at 08:59 PM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by Jacques1
    Like I said, you confuse identifier delimiters and string delimiters. For example, "option" is a string, so it must be enclosed in string delimiters: 'options'. When you write `option`, you tell MySQL it's a database object like a table or a column named "option", which is of course nonsense.

    So this is actually a syntax error.

    If you literally copy and paste this code into phpmyadmin, that's another error. phpmyadmin does not evaluate PHP code. You can only input actual MySQL queries.
    Well, this isn't my code. It's from a video player install script.

    I was hoping it was some sort of syntax error, and then when fixed the php script would work and when called would create the necessary table, value and fields.

    According to the designer, there is nothing wrong with the way the script is written or the queries.

    There is an admin page where you fill out a form and the username, password etc fields are supposed to be saved into the database.

    Submitting the form says everything is okay, but there is no data saved in the database.

    Investigating I saw that the fields weren't created and figured it was a query error.

    Update: Fixed.

    Turns out it was a character error.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by mattwmc
    Turns out it was a character error.
    What do you mean by that?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by Jacques1
    What do you mean by that?
    From this:

    Code:
    INSERT INTO `new__config` (`option`, `value`) VALUES (`username`, '".$username."'),
    (`password`, '".md5($password)."'), (`psword`, '".$password."'), (`email`, '".$email."'),
    (`script_url`, '".$scripturl."'), (`site_url`, '".$domain."');";
    to this works:

    Code:
    $sql = "INSERT INTO `new__config` (`option`, `value`) VALUES ('username', '".$username."'), ('password', '".md5($password)."'), ('psword', '".$password."'), ('email', '".$email."'), ('script_url', '".$scripturl."'), ('site_url', '".$domain."');";
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    The wrong quotes was what I was trying to tell you.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by Jacques1
    The wrong quotes was what I was trying to tell you.
    Lol. I was told it was due to my server not being able to read them.

    Which I guess makes sense.

IMN logo majestic logo threadwatch logo seochat tools logo