PHP Where clause syntax

Question

Please can someone explain what I have done wrong, there may also be further errors. (First attempt at using PHP with a tutorial). The tutorial did not specify a table name. The error is:

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 'WHERE userName = ''' at line 1

My Code:

<?php
//MySQL Database Setup
define('DB_HOST', '***');
define('DB_NAME', '***');
$db_table = "emailUser";
define('DB_USER','***');
define('DB_PASSWORD','***');

//Connection
$con=mysql_connect(DB_HOST,DB_USER,DB_PASSWORD) or die("Failed to connect to MySQL: " . mysql_error());
$db=mysql_select_db(DB_NAME,$con) or die("Failed to connect to MySQL: " . mysql_error());

//Connection Checking
//if (mysqli_connect_errno($con)) {
//    echo "Failed to connect to MySQL: " . mysqli_connect_error();
//  }
//else {
//      echo “Successfully connected to the AllColes webmail database…”;
//  }

function webmailNewUser()
{
    $userFullName = $_POST['webmailFullName'];
    $userName = $_POST['webmailUserName'];
    $userExEmail = $_POST['webmailExEmail'];
    $userPhone = $_POST['webmailPhone'];
    $userDOB = $_POST['userDOB'];
    $query = "INSERT INTO $db_table_name (userFullName,userName,userExEmail,userPhone,userDOB) VALUES ('$userFullName','$userName','$userExEmail','$userPhone','$userDOB')";
    $data = mysql_query ($query) or die(mysql_error());
    if ($data)
    {
        echo "Your registration for ColesMail is compleated.";
    }
    else
    {
        echo "Registration for ColesMail has NOT compleated succesfully!";
    }
}

function webmailSignUp()
{
    if(!empty($_POST['webmailUserName'])) 
    {
        $query = mysql_query("SELECT * FROM $db_table_name WHERE userName = '$_POST[user]'") or die(mysql_error()); //checking the same name in the field

        if(!$row = mysql_fetch_array($query) or die(mysql_error()))
        {
            webmailNewUser();
        }
        else
        {
            echo "Sorry someone already has this username";
        }
    }
}

function webmailForgottenPW()
{
                    //Email Requirements
                        $webMaster = 'christophercoles@live.co.uk';
                        $emailSubject = 'Forgotten Password Reset!';
                        $headers = "From: $webMaster\r\n";
                        $headers = "MIME-Version: 1.0\r\n";
                        $headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
                        $headers .= "X-Mailer: PHP/".phpversion();
                    //Forgotten Password Fields
                        $fpUserName = $_POST['webmailFPuserName'];
                        $fpContactMethod = $_POST['webmailFPcontactMethod'];
                        $fpDOB = $_POST ['webmailFPDOB'];
                    //Email Body
                        $body = "
                        <html>
                            <head>
                            <title>Password Reset for $fpUserName</title>
                            </head>
                            <body>
                            <h1>Password Reset</h1><hr>
                            Name: $fpUserName <br>
                            Request Password by: $fpContactMethod <br>
                            Date of Birth: $fpDOB
                            </body>
                        </html>";

                    //After Sending
                        mail($webMaster,$emailSubject,$body,$headers);
                        $theResults = "Sucess Email Sent!!!";
                        echo $theResults;
}

if(isset($_POST['webmailRegisterSubmit']))
{
    webmailSignUp();
}
if(isset($_POST['webmailForgottenPWSubmit']))
{
    webmailForgottenPassword();
}
?>

Don't use `mysql_*` extension [it's deprecated, and just generally bad news](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). Whatever tutorial you're using, I suggest you stop using it, and look for one that was written in this century (using `mysqli_*` or `PDO`) — Elias Van Ootegem, Feb 25 '14 at 10:11
`echo "SELECT * FROM $db_table_name WHERE userName = '$_POST[user]'";`, see what you're trying to execute. — deceze, Feb 25 '14 at 10:12
You also definitely must read this: [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/). It may actually be your problem. — deceze, Feb 25 '14 at 10:14
You should use Prepared Statements, your code is vulnerable for sql injections. Don't underestimate this danger, because it lets attackers destroy your database, so really fix it e.g. like this: https://php.net/manual/de/mysqli.quickstart.prepared-statements.php — alex, Feb 25 '14 at 10:15
Regarding `$db_table`, also read http://stackoverflow.com/a/16959577/476 — deceze, Feb 25 '14 at 10:19
@ManojPurohit: `$db_table` is _global_, all of the queries are performed in _functions_. Changing the name will still result in queries looking like `SELECT * FROM WHERE` — Elias Van Ootegem, Feb 25 '14 at 10:22
possible duplicate of [Reference: What is variable scope, which variables are accessible from where and what are "undefined variable" errors?](http://stackoverflow.com/questions/16959576/reference-what-is-variable-scope-which-variables-are-accessible-from-where-and) — deceze, Feb 25 '14 at 10:24
@deceze Nice article, I saw many questions today without any escaping! I shall recommend it next time :) — Birla, Feb 25 '14 at 10:24
@EliasVanOotegem that i know, i was just pointing to the typo and deceze has already made that point. — Manoj Purohit, Feb 25 '14 at 10:25
Thank you all for the comments; what I have taken away is: 1) I am using a depreciated code and need to Google changing MySQL_* to mysqli_*. 2) An echo SELECT statement which will tell me what is happening (for debugging). 3) My db_table / db_table_name variable had a typo. — Smokey, Feb 25 '14 at 10:58
I got a little lost with the great escapism PHP section, I'd like to learn but am not sure where in my document what your referring too and what I need to change and why. Remember first php document. — Smokey, Feb 25 '14 at 11:06

Elias Van Ootegem · Accepted Answer · 2014-02-25T10:30:35.440

3

You're using $db_table_name, but you only have a global $db_table variable.
Be that as it may, using all functions mean you don't have access to that global variable, pass it to the functions as an argument:

function webmailSignUp($db_table_name)
{//code here
}

Then, when you call these functions:

webmailSignUp($db_table);

Would address that issue. You might also want to check if your POST params are set (isset($_POST['user'])).
You also really have to look into injection vulnerabilities and, like I said, newer tutorials, that don't use a deprecated extension. Just read through the various comments to your question that link all over the web, some links deal with mysql_* being deprecated, others deal with variable scopes in PHP.

You could've spotted this error earlier (and you could've gotten a better error message), by setting your error reporting level to E_STRICT | E_ALL, which would've triggered an E_NOTICE undefined variable warning.
Either change your ini file to

error_reporting = E_STRICT | E_ALL
display_errors = 1

Or in your PHP code:

error_reporting(-1);
ini_set('display_errors',1);

Note: Only display errors while developing, never do this in production code.
Anyway, the reason why you do this is: notices are there to help you improve on your code: if there's an error, fix it. If there's a notice, pay notice.
In this case, you'd be told about your code trying to concatenate a non-existant variable into a string, That's a more helpful message than neigh on any mysql error message

edited Feb 25 '14 at 10:30

answered Feb 25 '14 at 10:21

Elias Van Ootegem

74,482
9
111
149

Dang, that was actually the problem. +1 MySQL's error messages are always messing with me, I'm always looking at the wrong spot. Like everyone else apparently. :) – deceze Feb 25 '14 at 10:23
@deceze: It took me some time, too. I was suspecting `$_POST['user']`, until I say the comments about `$db_table <> $db_table_name` :) – Elias Van Ootegem Feb 25 '14 at 10:25
You should also say something about error reporting, would have helped to spot that a lot earlier. – deceze Feb 25 '14 at 10:26
Thank you for spotting out the typo, $db_table was actually meant to be $db_table_name as a global variable only. However I didn't realise you could call and pass variables like that so will amend. Does the error reporting in the PHP code matter where it goes, can it go at the very end of the document? I just test my PHP after upload to a server in Internet Explorer. – Smokey Feb 25 '14 at 11:01
@Smokey: Functions and function arguments (passing vars) go together like a horse and carriage (*singing* love and marriage), you can't have one without the other. Where you place the `error_reporting` call is not specified in the docs, but I've always seen it placed at the very _top_ of a script. Still, setting the php.ini file is the safe option. Whichever way you go, calling the function will only affect the current script, changing the actual ini file will affect the entire PHP runtime. Also note that PHP5.4 >=, `E_STRICT` is part of `E_ALL`, so there's no need for the bitwise `|` operator – Elias Van Ootegem Feb 25 '14 at 11:44
Just check [the documentation](http://www.php.net/error_reporting) for details. also check the documentation below it, on `display_errors`, on how you can pipe errors to the stderr stream, or to a log file – Elias Van Ootegem Feb 25 '14 at 11:45

score 1 · Answer 2 · answered Feb 25 '14 at 10:25

1

Please try debugging with:

echo $_POST['user'];

Check if returns anything. I think you are looking into you database with an empty WHERE statement.

Or you could try and test your query in your database.

echo $query;

answered Feb 25 '14 at 10:25

Wesley De Keirsmaeker

1,026
9
29

score 0 · Answer 3 · answered Feb 25 '14 at 10:09

0

try this

mysql_query("SELECT * FROM $db_table_name WHERE userName = '".$_POST['user']."'")

answered Feb 25 '14 at 10:09

naveen goyal

4,571
2
16
26

@deceze hey dude here change in '' is missing in $_POST. I mean index of $_POST – naveen goyal Feb 25 '14 at 10:12
1

Sorry, what? `"$foo[bar]"` works fine in PHP, it's simple string interpolation syntax. – deceze Feb 25 '14 at 10:15
I have change this $_POST[user] to $_POST['user'] – naveen goyal Feb 25 '14 at 10:17
Yes you did, and it's unnecessary. – deceze Feb 25 '14 at 10:18
@naveengoyal: read the comments below Jenz's answer: _that_ is a syntax error – Elias Van Ootegem Feb 25 '14 at 10:18

score 0 · Answer 4 · answered Feb 25 '14 at 10:19

0

you have two different $table

you defined $db_table = "emailUser"; and you used $db_table_name

try this

  $query = mysql_query("SELECT * FROM $db_table 
                        WHERE userName = '".$_POST['user']."'") or die(mysql_error()); //checking the same name

answered Feb 25 '14 at 10:19

echo_Me

37,078
5
58
78

1

Actually, that still won't help by itself: http://stackoverflow.com/a/16959577/476 – deceze Feb 25 '14 at 10:20

Birla · Answer 5 · 2014-02-25T10:22:27.617

-1

Line 3, change define('DB_HOST', '***); to define('DB_HOST', '***'); (missing a quote).

I ran the code (here, commented the connection) and it doesn't produce any error. The only issue I could find was:

if(!empty($_POST['webmailUserName'])) 
{
    $query = mysql_query("SELECT * FROM $db_table_name WHERE userName = '$_POST[user]'") or die(mysql_error()); //checking the same name in the field

You are checking and using a different variable in the query. This however, wouldn't cause the syntax error.

edited Feb 25 '14 at 10:22

answered Feb 25 '14 at 10:11

Birla

1,170
11
30

That would not produce that error. Come on, that was a copy and paste mistake here on SO. – deceze Feb 25 '14 at 10:11

score -2 · Answer 6 · edited Feb 25 '14 at 11:47

-2

your problem occurs because of your query is wrong just put double quote " " instead of single '' like this

 $query = mysql_query("SELECT * FROM $db_table_name WHERE
 userName = ".$_POST['user']."") or die(mysql_error());

edited Feb 25 '14 at 11:47

naveen goyal

4,571
2
16
26

answered Feb 25 '14 at 10:25

dev4092

2,820
1
16
15

score -4 · Answer 7 · answered Feb 25 '14 at 10:12

-4

You missed out the single quotes for post variable ($_POST['user'])

$query = mysql_query("SELECT * FROM $db_table_name WHERE userName = '$_POST['user']'") or die(mysql_error());

answered Feb 25 '14 at 10:12

Jenz

8,280
7
44
77

3

*Now* it's a PHP syntax error, the original already worked fine. – deceze Feb 25 '14 at 10:12

PHP Where clause syntax

7 Answers7