Thread: Query Error

Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2

    Query Error


    I have some troubles with MySql query.
    The thing i need to do is to place the user_ip in my db for checking of attendance.
    So i have a few problems :
    1) My database doesn't reply to my queries. (UNCHANGEABLE)\
    2) Function for getting of the information from my db is showing the mistake. I know what does it means . But i can't understand how can i fix it.
    Thanks for all the answers that you will send to me .
    P.S.
    sorry for my English , it's not my native language.
    here is the picture :
    http://s004.radikal.ru/i205/1304/ed/c9adcc86b89f.jpg
    if previous failed :
    http://radikal.ru/F/i066.radikal.ru/1304/46/5997ec102762.jpg.html
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    mydb->query('INSERT INTO ipt (ip) VALUES ($userip)');// This doesn't works.
    the value is supposed to be a string, yes? like 225.121.4.156, yes? because the ip column is VARCHAR, yes?

    so strings need to be enclosed in quotes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2
    THANK YOU!!! I'm beginer in this. But u see my ip still can't get to the base .

    $mydb->query('INSERT INTO ipt (ip) VALUES ("$userIP")');

    i wrote to query like this and it MUST input atleast my ip to the base (127.0.0.1). there is only a new mistake (syntax error, unexpected T_VARIABLE). May be i need to give you more information . or i just need to read some more manuals ? i can't see the mistake .

    By the way : when i'm deliting or inserting the query phpMyAdmin generates queries in such way : INSERT INTO `questiondb`.`ipt` (`ip`) VALUES (`bla`)

    i tried to make an input in such way . but it doesn't work.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    i can't help you with the php myself but i can move this thread over to the php forum for you

    as for the `backticks` that you see in phpmyadmin, you may safely ignore them, they are not needed and not worth the bother
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

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

    there are several issues in your code.

    • Do not insert variables directly into query strings. Check the security tutorial in my signature. You need to use prepared statements instead.
    • You cannot insert variables into single quoted strings, anyway. That's only possible with doubled quoted strings (but like I said: don't do it with queries).
    • $print_r() is print_r(). This is a function call.


    The T_VARIABLE stuff is a syntax error, but I need to see the current code for that. Don't make screenshots, just post the code and wrap it in [ PHP ] tags.

    Comments on this post

    • FAQer agrees : His answers helped me to made my code work.
    Last edited by Jacques1; April 5th, 2013 at 01:11 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".
  10. #6
  11. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,960
    Rep Power
    9397
    Also, backticks ` are only for database, table, and field names, and only really needed when the name is a reserved word (like "select" or "order"). For strings you use regular "s and 's.

    ...except prepared statements are better, as Jacques mentioned.
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1045
    Personally, I think the backticks are actually harmful: They make the query less readable; you shouldn't use reserved words and exotic characters in the first place; they can lead to weird errors like `foo.bar` vs. `foo`.`bar`; and they only exist in MySQL, other database systems don't have them. So I wouldn't use them at all.
    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".
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2
    Thank to you people ,you helped me very much . I'll try to fix all the issues and post the feedback !
  16. #9
  17. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,960
    Rep Power
    9397
    Originally Posted by Jacques1
    and [backticks and/or quoting names] only exist in MySQL, other database systems don't have them.
    Except SQL Server
    Delimited identifiers

    Are enclosed in double quotation marks (") or brackets ([ ]). Identifiers that comply with the rules for the format of identifiers might not be delimited. For example:
    Code:
    SELECT *
    FROM [TableX]         --Delimiter is optional.
    WHERE [KeyCol] = 124  --Delimiter is optional.
    Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement. For example:
    Code:
    SELECT *
    FROM [My Table]      --Identifier contains a space and uses a reserved keyword.
    WHERE [order] = 10   --Identifier is a reserved keyword.
    and PostgreSQL
    There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected. The example can be written with quoted identifiers like this:

    Code:
    UPDATE "my_table" SET "a" = 5;
    Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.
    and Oracle
    Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

    * A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

    * A nonquoted identifier is not surrounded by any punctuation.
    and if you don't like those, SQLite will really annoy you
    If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:

    * 'keyword' A keyword in single quotes is a string literal.
    * "keyword" A keyword in double-quotes is an identifier.
    * [keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
    * `keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

    Comments on this post

    • ManiacDan agrees
    • FAQer agrees : Useful inormation
    • Jacques1 disagrees : I said *backticks*. Please don't put words in my mouth in order to "disprove" me. Thanks.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2
    THANK YOU! IT WORKS NOW! (50% works)
    PHP Code:
     $userIP $_SERVER["REMOTE_ADDR"];  
    $mydb = @ new mysqli('localhost','root','','questiondb');
    if (
    mysqli_connect_errno())
     {
        echo 
    "Conection error:".mysqli_connect_error();
     }
     
    $mydb->query('INSERT INTO ipt (ip) VALUES ("$userIP")'); 
    $userIP is still avoids db . but i think it's about the "prepared statements"(Jacques1). that query inserts th string -> "$userIP" to the base , or alerts about the error so must be you are right .
    Thanks to you all again .
  20. #11
  21. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    That still won't work, you want either prepared statements or:

    PHP Code:
     $mydb->query('INSERT INTO ipt (ip) VALUES ("' mysqli_real_escape_string($userIP) . '")'); 
    Assuming $mydb has opened a mysqli connection.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  22. #12
  23. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,960
    Rep Power
    9397
    I'll be the crazy one and point it out:

    You don't actually have to escape the IP address.

    The address is, technically speaking, provided by the client, but it follows a very definite format that will always be obeyed due to the nature of TCP/IP: numbers and periods for IPv4, hex digits and colons for IPv6. Neither of those are any threat to SQL.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2009
    Location
    Nebraska, USA
    Posts
    862
    Rep Power
    275
    I'm assuming since the GLOBAL $_SERVER[] is being used, and not, $_GET[], $_POST[], or $_REQUEST[], that this, also, wouldn't be needed.
    PHP Code:
    $userIP $_SERVER["REMOTE_ADDR"];
     
    $userIP filter_var$userIPFILTER_VALIDATE_IP ); 
  26. #14
  27. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,960
    Rep Power
    9397
    Some things in $_SERVER do need to be validated. The REMOTE_ADDR specifically does not.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2
    True to say
    this way shows a mistake about the parameters :
    PHP Code:
     $mydb->query('INSERT INTO ipt (ip) VALUES ("' mysqli_real_escape_string($userIP) . '")'); 
    and this way doesn't work either :
    PHP Code:
     $userIP filter_var$userIPFILTER_VALIDATE_IP ); 
    it seems that var. can't be there . i need to take clear IP adress (as a literal). i'll try the prepared statements .and post you the feedback for you .
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo