CODEWITHSUNDEEP
phpmysqlwindowswampserver
user1927431
user1927431

Reputation: 43

MySQL syntax error with PHP

I'm having a problem with MySQL 5.5.24 and PHP 5.4.3. I have the following HTML/PHP Code:

<?php
function connect() {
    $con = mysql_connect( "127.0.0.1", "root", "" );
    if ( !$con ) {
        die( "Could not connect: " . mysql_error() );
    }
    mysql_select_db( "game" );    
}

function protect( $string ) {
    return mysql_real_escape_string( strip_tags( addslashes( $string ) ) );
}


if ( isset( $_POST[ 'register' ] ) ) {
    connect();
    $username = protect( $_POST[ 'username' ] );    
    $register = mysql_query( "SELECT 'id' FROM 'user' WHERE 'username'='$username'" ) or die( mysql_error() );
    if ( mysql_num_rows( $register ) > 0 ) {
        echo "That username is already in use!";
    } else {
        $ins = mysql_query( "INSERT INTO 'user' ('username') VALUES ('$username')" ) or die( mysql_error() );
        echo "You have registered!"; 
    } 
}
?>
<form action="register.php" method="POST">           
Username: <input type="text" name="username"/><br/>
<input type="submit" name="register" value="Register"/>
</form>

Whatever I input, MySQL will always return 

"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 ''user' WHERE 'username'='XXX'' at line 1"

I checked these queries with phpmyadmin and they work just fine! There is also no connection problem. I even tried plain "SELECT * FROM 'user'" with the same result. I simply do not get any response, just the mentioned error about malformed SQL syntax.

Now I checked tons of articles having the same error and in each case the problem was having used a keyword of PHP or missing/mispelling a variable name and that kind. I checked my statements a hundred times now and I can't find any error. I'm quiet new to PHP so maybe I miss something...

One of my last experiments was changing the collation of the database as well as the storage engine, also with no result...

Hope you guys see what I can't! :-)

BTW, I'm running Windows 7 and WAMP 2.2.

Upvotes: 4

Views: 792

Answers (2)

Mark Byers
Mark Byers

Reputation: 838276

You should use backticks (not quotes) when writing names:

SELECT `id` FROM `user` WHERE `username` = 'foo'

Quotes are for string literals.

By the way your protect function is a bad idea. I would recommend that you avoid using the deprecated mysql_* functions. Use parameterized queries in either mysqli or PDO.

Related

Upvotes: 1

galchen
galchen

Reputation: 5290

SELECT 'id' FROM 'user' WHERE 'username'

Change ' to ` (back quote)

what you are talking about table names and column names you should use backquote. when you are talking about strings (like values) then use single/double quotes

Upvotes: 3

Related Questions