Simple table query syntax error?

Question

I hope someone here can help me see where this error is coming from.

I have a form with two fields: email and password. The form's action takes it to a php page that is supposed to

start a session
connect to a database via mysql
run a query and select a row in a table where the email field is similar to the email submitted in the form.

At this stage it is incomplete, I only echo some of the fields at the end of the script to see if it works.

I tested it and there was an unexpected end error that came up right at the last line; a bracket I left out. So I though there would be no other errors, but then when I tested it again I got this 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 '@gmail.com' at line 6

@gmail.com is the last bit of the email I submitted.

Here is the code of the php (action) page:

<?php
session_start();
$_SESSION['sessionemail'] = $_POST['email'];
$_SESSION['sessionpassword'] = $_POST['password'];
$_SESSION['authuser'] = 0;

$dbhost = 'somewhere.com';
$dbuser = 'user';
$dbpass = 'pw';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'medreunten_db1';
mysql_select_db($dbname) or die(mysql_error($conn)); 


$query = 'SELECT
    name, smacker, surname, sex, age, nationality, email
    FROM
    employee
    WHERE
    email = ' . $_POST['email'];
$result = mysql_query($query, $conn) or die (mysql_error($conn));
extract(mysql_fetch_assoc($result));  


while ($row = mysql_fetch_array($result)) {
echo $row['name'];
echo $row['surname'];
echo $row['age'];
}
?>

I tried removed the first 5 lines and I still got the same error.

Somehow, when the php gets parsed, the browser reads the content of the email variable as if it is part of my php code. At least that's what I thought because the error I receive states that there is a problem with the syntax near "@gmail.com".

I hope someone can give me a clue!

possible duplicate of [How to include a PHP variable inside a mysql insert statement](http://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-insert-statement) — Your Common Sense, Sep 25 '11 at 14:19
You would need to do something like `\''.mysql_real_escape_string('$_POST['email']).'\'';` Note the single quotes. — Jared Farrish, Sep 25 '11 at 14:21

score 2 · Accepted Answer · answered Sep 25 '11 at 14:23

2

You have an SQL injection, always apply mysql_real_escape_string() to any user-submitted or otherwise potentially tampered-with data before sending to a MySQL database.

Note the ' around the email variable.

$email = mysql_real_escape_string($_POST['email']);

$query = "
SELECT name, smacker, surname, sex, age, nationality, email
FROM employee
WHERE email = '$email'
";

answered Sep 25 '11 at 14:23

Jared Farrish

48,585
17
95
104

mysql_real_escape_string doesn't "clean" anything and has nothing to do with user input. Didn't I tell you that already? – Your Common Sense Sep 25 '11 at 14:25
both his error and mysql_real_escape_string has nothing to do with injections. it's SQL syntax. – Your Common Sense Sep 25 '11 at 14:39
Sure. And the user may not assume that a submitted email might have `'`; after all, if they were doing an insert, *they* would not put an extra `'` in the email. It's a *practice* to use `mysql_real_escape_string()`, not a *requirement*. The point is that when dealing with user-submitted content, be careful to use an appropriate method. It is not *wrong* to suggest this. Not doing so, though, can lead to real problems. – Jared Farrish Sep 25 '11 at 14:41
no, it's requirement. and limiting yourself to escaping only user input you'll end up in deep trouble. – Your Common Sense Sep 25 '11 at 14:55
I did not say "only escape user-submitted information", you did. Additionally, if I hand-type or otherwise have data I *know* not to have stray `'`, I am not *required* to use `mysql_real_escape_string()`. User-submitted and otherwise unsafe data is a subset of the kinds of data that may be submitted in a query, but it is an important one, and one worth mentioning to someone who clearly has one in a query. `:)` – Jared Farrish Sep 25 '11 at 15:04
`always apply mysql_real_escape_string() to any user-submitted` are your exact words. there is a simple choice - escape every string coming to the query or just some of them. You didn't say "every". So, you doesn't mean that. So, you're in trouble, silly boy. – Your Common Sense Sep 25 '11 at 15:07
not to mention that mysql_real_escape_string() is just useless for any SQL part type beside strings. So, it's just insufficient to protect from injection. Your answer is misleading. – Your Common Sense Sep 25 '11 at 15:13
Not mentioning it is not the same as saying "only", especially since it is *not* **always** required to build a query using PHP variables. This is an asinine argument on a difference without a distinction. – Jared Farrish Sep 25 '11 at 15:13

score -1 · Answer 2 · answered Sep 25 '11 at 14:22

<?php
session_start();
$_SESSION['sessionemail'] = $_POST['email'];
$_SESSION['sessionpassword'] = $_POST['password'];
$_SESSION['authuser'] = 0;

$dbhost = 'dedi147.cpt2.host-h.net';
$dbuser = 'medreunten_1';
$dbpass = 'AGqrVrs8';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'medreunten_db1';
mysql_select_db($dbname) or die(mysql_error($conn)); 


$query = "SELECT name, smacker, surname, sex, age, nationality, email FROM employee WHERE email = '" . $_POST['email']."'";
$result = mysql_query($query, $conn) or die (mysql_error($conn));
extract(mysql_fetch_assoc($result));  


while ($row = mysql_fetch_array($result)) {
echo $row['name'];
echo $row['surname'];
echo $row['age'];
}
?>

try this, should work

Simple table query syntax error?

2 Answers2

Linked