MySQL syntax error with PHP

Question

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.

Generic filtering functions are bad. mysql_ is also bad. Give PDO and prepared statements a try! — Ry-
– Ry- ♦, Commented Dec 25, 2012 at 0:12
for the table names, either use back ticks `, or don't use any quotes for table names at all: SELECT id FROM user... — kennypu
– kennypu, Commented Dec 25, 2012 at 0:13
Specifically, code like yours is the reason my last name always comes out as O\\\'Hara. Please don’t. — Ry-
– Ry- ♦, Commented Dec 25, 2012 at 0:14

galchen · Accepted Answer · 2012-12-25 00:15:00Z

3

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

edited Dec 25, 2012 at 0:15

answered Dec 25, 2012 at 0:12

galchen

5,3003 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:56:41Z

1

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

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Dec 25, 2012 at 0:12

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

MySQL syntax error with PHP

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related