Thread: Php mysql error

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

    Join Date
    Jul 2013
    Posts
    15
    Rep Power
    0

    Php mysql error


    Hi guys i have the following code:
    PHP Code:
    $mysql_query "SELECT * FROM useri WHERE username = '$username'";
                                                                      
                                                                      
        
    $result mysql_query($mysql_query);                          
        if(
    $result === false){                                        
            echo 
    mysql_error();                                       
        } 
    and i get the following 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 ''useri' WHERE username = 'asd'' at line 1.

    Can you pleae help me build the correct sql syntax.
    I submit my $username variable via $_POST.
    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,031
    Rep Power
    377
    can you maybe echo out the query and then do it directly in mysql using phpmyadmin, from what i can see i dont think there is any problem with the sql query itself.

    However [jacques] you should be using pdo or mysqli, your code is open to injection if you are not validating user input and putting it directly into the query [/jacques]
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    15
    Rep Power
    0

    my complete code


    Thanks for the answer, i don'y use pdo or mysli cause i'm working with an old IDE that dosen't suport php 5.5 syntax, and i didin't got to understand pretty well classes so i don't actualy how to use PDO. i will post my complete code, maybe you will get a better look at what i am doing:

    PHP Code:
    <?php

    //use the data sent from form
    //first check database connection
    $conn mysql_connect("localhost","root");
    if(!
    $conn){
        die(
    "I can't acces db " mysql_error());
    }
    //select db to work with
    $select_db mysql_select_db("agenda_test"$conn);
    if (!
    $select_db){
        die(
    "Db cannot be selected" mysql_error);
    }

    //Take the values sent from POST and get them in to variables
    $nume $_POST['nume'];
    $prenume $_POST['prenume'];
    $username $_POST['username'];
    $parola $_POST['parola'];
    $submit $_POST['submit'];
    //check if data is valid
    if (empty($nume) || empty($prenume) || empty($username) || empty($parola)){

        echo 
    "You have't completed all the required fields " "<br />";
        echo 
    "<a href = \"form_inregistrare.htm\">Please try again!</a>";

    }
    //check if the username already exist's
    $mysql_query "SELECT * FROM useri WHERE username = '$username'";
        
        
        
    $result mysql_query($mysql_query);
        if(
    $result === false){ 
            echo 
    mysql_error();
        } elseif(
    $result === 1) {
            echo 
    "This username already exists in db." "<br /";
            echo 
    "To chose another username, " "<a href = \"form_inregistrare.htm\>click here<a>";
        } 
     else {
        echo 
    "Thank you!";
    }


    ?>
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    Try using `` on the table name
    PHP Code:
    $mysql_query "SELECT * FROM `useri` WHERE username = '$username'";
                                                                      
                                                                      
        
    $result mysql_query($mysql_query);                          
        if(
    $result === false){                                        
            echo 
    mysql_error();                                       
        } 
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    15
    Rep Power
    0

    almost there


    "dedicatedzone" i tried that and dosen't had any effect. I almost managed to get it done like this:

    PHP Code:
    //check if the username already exist's
    $mysql_query "SELECT * FROM useri WHERE username = '$username'";
        
        
        
    $result mysql_query($mysql_query);
        if(
    mysql_affected_rows($result) <= 1){
            echo 
    "This username already exists in db." "<br /" .
            
    "To chose another username, " "<a href = \"form_inregistrare.htm\">click here<a>";
        
    } else {
        echo 
    "Thank you!";

    it works but i get a warning message:

    Warning: mysql_affected_rows(): supplied resource is not a valid MySQL-Link resource in C:\xampp\htdocs\Viorel agenda\prelucrare_form.php on line 33


    Any other ideeas would be really appreciated! Thanks!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,031
    Rep Power
    377
    did you try my suggestion:

    can you maybe echo out the query and then do it directly in mysql using phpmyadmin, from what i can see i dont think there is any problem with the sql query itself.

    AND you do not do mysql_affected_rows() on a SELECT, its used for update/inserts/delete
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    Can you post results from

    PHP Code:
    var_dump($mysql_query,$result); 
    this would be right after
    PHP Code:
    $result mysql_query($mysql_query); 
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    15
    Rep Power
    0
    @paulh1983 thank you for your answers, i used mysql_affected_rows because i run out of ideeas. About the query, i submited "asd" using my form, my query echo was "SELECT * FROM useri WHERE username = 'asd'" and the result that i get from phpmy admin running this query was "MySQL returned an empty result set (i.e. zero rows). "

    @dedicatedzone thank you for your answers. Running this code:


    PHP Code:
    //check if the username already exist's
    $mysql_query "SELECT * FROM useri WHERE username = '$username'";
        
    var_dump($mysql_query,$result); 

        
        
    $result mysql_query($mysql_query);
        if(
    $result === false){ 
            echo 
    mysql_error();
        } elseif(
    $result === 1) {
            echo 
    "This username already exists in db." "<br /";
            echo 
    "To chose another username, " "<a href = \"form_inregistrare.htm\>click here<a>";
        } 
     else {
        echo 
    "Thank you!";

    i get this:
    Notice: Undefined variable: result in C:\xampp\htdocs\Viorel agenda\prelucrare_form.php on line 30
    string(42) "SELECT * FROM useri WHERE username = 'asd'" NULL Thank you!


    Any ideea's? Thank's.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    So your query is:

    SELECT * FROM useri WHERE username = 'asd'

    I would still use `useri` and you get null as the result.

    if you want to check simply if user exists I would do:

    PHP Code:
    $mysql_query "SELECT count(username) as exists FROM `useri` WHERE username = '$username'"
    $result mysql_query($mysql_query); 
    // now this will show you what you get from the server for debugging only
    var_dump($mysql_query,$result);
    if(!
    $result)  // 
    {
        echo 
    mysql_error();
    }
    $row mysql_fetch_row($result);
    if(
    $row[0] > 0) {
            echo 
    "This username already exists in db." "<br /"
            echo 
    "To chose another username, " "<a href = \"form_inregistrare.htm\>click here<a>"
        }  else {
       echo 
    "Thank you!"

    Comments on this post

    • paulh1983 agrees : although you are trying to help, i wouldnt just give out answers straight away as it doesnt teach them anything! let them think for a bit or guide them
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    15
    Rep Power
    0
    Yes i just want to se if username exist so i can redirect to registration page to try a new one! Thank you!
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    *sigh*

    You "web programmers" are like 2-year-olds: You don't understand that a hot plate is dangerous until you've actually burned your fingers on it.

    I've explicitly told you that your code is insecure in your last thread (the one you locked). paulh1983 repeated and detailed this warning in this thread. Yet you're still running around with your completely unprotected queries, because obviously you cannot believe that some people on this planet may have bad intentions.



    Originally Posted by Viorel1989
    Thanks for the answer, i don'y use pdo or mysli cause i'm working with an old IDE that dosen't suport php 5.5 syntax, and i didin't got to understand pretty well classes so i don't actualy how to use PDO.
    First of all, there's no such thing as "PHP 5.5 syntax". If you're talking about objects, those exist since PHP 4. That was in 2000! If your "IDE" hasn't been updated since 2000, you need a new one. There are many excellent IDEs like Netbeans or Eclipse, and they're completely free.

    Secondly, paulh1983 told you about the MySQLi extension, which has a classical procedural interface. You don't need objects to write modern database code.

    Third, which database extension you choose has absolutely nothing to do with your obligation to protect your users and your server. The code must be secure in any case. If you choose to ignore our warnings, then you deliberately put other people at risk.
    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".
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    15
    Rep Power
    0
    Hello again.

    I tried @dedicatedzone method but my code still dosen't work. I will post my complete code again and the html form. My database has just 1 record that i'm tring to work with.

    PHP Code:
    <?php

    //use the data sent from form
    //first check database connection
    $conn mysql_connect("localhost","root");
    if(!
    $conn){
        die(
    "I can't acces db " mysql_error());
    }
    //select db to work with
    $select_db mysql_select_db("agenda_test"$conn);
    if (!
    $select_db){
        die(
    "Db cannot be selected" mysql_error);
    }

    //Take the values sent from POST and get them in to variables
    $nume $_POST['nume'];
    $prenume $_POST['prenume'];
    $username $_POST['username'];
    $parola $_POST['parola'];
    $submit $_POST['submit'];
    //check if data is valid
    if (empty($nume) || empty($prenume) || empty($username) || empty($parola)){

        echo 
    "You have't completed all the required fields " "<br />";
        echo 
    "<a href = \"form_inregistrare.htm\">Please try again!</a>";

    }
    //check if the username already exist's
    $mysql_query "SELECT count(username) as exists FROM useri WHERE username = '$username'";
    //using the query we check if there is a record like the one from the variable $username
    //if the result is true, we redirect user to choose another username
    //else continue with this valid username      

        
     
    $mysql_query "SELECT count(username) as exists FROM `useri` WHERE username = '$username'";  
    $result mysqli_query($mysql_query);  
    // now this will show you what you get from the server for debugging only 
    var_dump($mysql_query,$result); 
    if(!
    $result)  //  

        echo 
    mysqli_error(); 

    $row mysqli_fetch_row($result); 
    if(
    $row[0] > 0) { 
            echo 
    "This username already exists in db." "<br /";  
            echo 
    "To chose another username, " "<a href = \"form_inregistrare.htm\>click here<a>";  
        }  else { 
       echo 
    "Thank you!";  





    ?>

    and the form:

    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    
    <head>
    	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    	<meta name="author" content="Viorel" />
    
    	<title>Formular inregistrare utilizator nou.</title>
    </head>
    
    <body>
        <h1>Aceasta este pagina unde iti poti creea un nou cont.</h1>
        <p>Pentru a te putea inregistra te rog sa completezi urmatorul formular.</p>
        <form action="prelucrare_form.php" method="POST" ><br />
        Numele tau:<br /><input type="text" name="nume" /> <br />
        Prenumele tau: <br /><input type="text" name="prenume"/> <br />
        Usernameul dorit: <br /><input type="text" name="username"/><br />
        Parola: <br /><input type="password" name="parola"/><br />
        <input name="submit" type="submit" value="Trimite date."/>
        </form>
    
    </body>
    </html>
    and the error that i get is:

    Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\xampp\htdocs\Viorel agenda\prelucrare_form.php on line 36
    string(71) "SELECT count(username) as exists FROM `useri` WHERE username = 'Viorel'" NULL
    Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\xampp\htdocs\Viorel agenda\prelucrare_form.php on line 41

    Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\Viorel agenda\prelucrare_form.php on line 43
    Thank you!



    and that is the error that i get whwn i submit a username value that already exist's in my db.

    Comments on this post

    • Jacques1 disagrees : S - E - C - U - R - I - T - Y -- Your code still doesn't have it.
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    You cannot mix the new MySQLi extension with the old MySQL extension. And you can't just append an "i" to convert the mysql_* functions into the corresponding mysqli_* functions -- what do you gain from that, anyway? The benefit of the new extension lies in the new (security) features! If you stick to your old insecure practices, the new extension doesn't help you.

    So you've decided to switch to MySQLi? That's an excellent decision. To give you a start, this is how you use the extension correctly. I'm using the object-oriented interface, because I find it more intuitive. But it should be pretty easy to translate this into the procedural interface with the help of the PHP manual:

    PHP Code:
    <?php

    // --------- connect to database; this should be in a separate file: ---------

    // throw exceptions in case of errors
    $mysqli_driver = new mysqli_driver();
    $mysqli_driver->report_mode MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT;

    $database = new mysqli('localhost''YOURUSER''YOURPASSWORD''YOURDB');



    $test_user 'whatever';

    // check if user exists in database; use a prepared statement to securely pass values to the query

    // create prepared statement (the question mark is a placeholder)
    $user_exists_stmt $database->prepare('
        SELECT
            EXISTS (
                SELECT
                    1
                FROM
                    useri
                WHERE
                    username = ?
            )
    '
    );

    // pass value of $test_user to query (as a string)
    $user_exists_stmt->bind_param('s'$test_user);

    // execute statement
    $user_exists_stmt->execute();

    // bind result to variable $user_exists
    $user_exists_stmt->bind_result($user_exists);

    // fetch result into $user_exists
    $user_exists_stmt->fetch();

    if (
    $user_exists)
        echo 
    'User exists!';
    else
        echo 
    'User does not exist!';
    Several things:

    • Use prepared statements whenever you wanna pass values to a query. Do not just dump variables into the query string.
    • Don't dump error messages on the screen. This will irritate legitimate users and help attackers. Whether or not you're having issues with your database is none of your users' business. Either use trigger_error to generate an error message (which will be written to the proper device according to the php.ini). Or use exceptions as I do above.
    • Format your queries!
    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".

IMN logo majestic logo threadwatch logo seochat tools logo