Can't figure out what's wrong with my php/sql statement

Question

So this is probably a dumb beginner question, but I've been looking at it and can't figure it out. A bit of background: just practicing making a web app, a form on page 1 takes in some values from the user, posts them to the next page which contains the code to connect to the DB and populate the relevant tables.

I establish the DB connection successfully, here's the code that contains the query:

$conn->query("SET NAMES 'utf9'");         

$query_str = "INSERT INTO 'qa'.'users' ('id', 'user_name','password' ,'email' ,'dob' ,'sx') VALUES (NULL, $username, $password, $email, $dob, $sx);";

$result = @$conn->query($query_str);

Here's the error that is returned:Insert query failed: 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 ''qa'.'users' ('id', 'user_name' ,'password' ,'email' ,'dob' ,'s' at line 1

Thanks in advance!

Answer 1

Unless it's changed since I did MySQL in PHP, escape your db/column/table names with backticks (`), not apostrophes (').

Answer 2

A good general trouble-shooting technique is to make the query work via another interface to the database. For example, phpMyAdmin. If it works there, you have some confidence going forward. or you may find how to fix your SQL. (phpMyAdmin is handy because it will convert your SQL into a ready-made string for PHP.)

Answer 3

You need to escape your column names with a backtick (`) instead of (')

You also need to properly escape the actual values you are inserting as well (use a single quote)

Answer 4

OMG not a single right answer

$query_str = "
INSERT INTO `qa`.`users` (`id`, `user_name`,`password` ,`email` ,`dob` ,`sx`) 
VALUES (NULL, '$username', '$password', '$email', '$dob', '$sx')";

identifiers being quoted with backticks, while strings being quoted with apostrophes!

and I hope you have passed all your variables through mysql_real_escape string BEFORE putting it into query, i.e.:

$username = mysql_real_escape string($username);

and so on