phpmyAdmin errors I don't understand

Question

Today I was using this tutorial about creating a forum with a sign up using phpmyAdmin. It was going great until I reached this line of code, which I was supposed to enter in the SQL.

1. INSERT INTO
2.        users(user_name, user_pass, user_email ,user_date, user_level)
3. VALUES('" . mysql_real_escape_string($_POST['user_name']) . "',
4.        '" . sha1($_POST['user_pass']) . "',
5.        '" . mysql_real_escape_string($_POST['user_email']) . "',
6.        NOW(),   
7.        0);

So I entered it in phpmyAdmin and I get this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'user_name']) . "', '" . sha1($_POST['user_pass']) . "', '" . mys' at line 3

And this is the PHP I used for signup.php

<?php
//signup.php
include 'connect.php';
include 'header.php';

echo '<h3>Sign up</h3>';

if($_SERVER['REQUEST_METHOD'] != 'POST')
{
    /*the form hasn't been posted yet, display it
      note that the action="" will cause the form to post to the same page it is on */
    echo '<form method="post" action="">
        Username: <input type="text" name="user_name" />
        Password: <input type="password" name="user_pass">
        Password again: <input type="password" name="user_pass_check">
        E-mail: <input type="email" name="user_email">
        <input type="submit" value="Add category" />
     </form>';
}
else
{
    /* so, the form has been posted, we'll process the data in three steps:
        1.  Check the data
        2.  Let the user refill the wrong fields (if necessary)
        3.  Save the data
    */
    $errors = array(); /* declare the array for later use */

    if(isset($_POST['user_name']))
    {
        //the user name exists
        if(!ctype_alnum($_POST['user_name']))
        {
            $errors[] = 'The username can only contain letters and digits.';
        }
        if(strlen($_POST['user_name']) > 30)
        {
            $errors[] = 'The username cannot be longer than 30 characters.';
        }
    }
    else
    {
        $errors[] = 'The username field must not be empty.';
    }


    if(isset($_POST['user_pass']))
    {
        if($_POST['user_pass'] != $_POST['user_pass_check'])
        {
            $errors[] = 'The two passwords did not match.';
        }
    }
    else
    {
        $errors[] = 'The password field cannot be empty.';
    }

    if(!empty($errors)) /*check for an empty array, if there are errors, they're in this array (note the ! operator)*/
    {
        echo 'Uh-oh.. a couple of fields are not filled in correctly..';
        echo '<ul>';
        foreach($errors as $key => $value) /* walk through the array so all the errors get displayed */
        {
            echo '<li>' . $value . '</li>'; /* this generates a nice error list */
        }
        echo '</ul>';
    }
    else
    {
        //the form has been posted without, so save it
        //notice the use of mysql_real_escape_string, keep everything safe!
        //also notice the sha1 function which hashes the password
        $sql = "INSERT INTO
                    users(user_name, user_pass, user_email ,user_date, user_level)
                VALUES('" . mysql_real_escape_string($_POST['user_name']) . "',
                       '" . sha1($_POST['user_pass']) . "',
                       '" . mysql_real_escape_string($_POST['user_email']) . "',
                        NOW(),
                        0)";

        $result = mysql_query($sql);
        if(!$result)
        {
            //something went wrong, display the error
            echo 'Something went wrong while registering. Please try again later.';
            //echo mysql_error(); //debugging purposes, uncomment when needed
        }
        else
        {
            echo 'Successfully registered. You can now <a href="signin.php">sign in</a> and start posting! :-)';
        }
    }
}

include 'footer.php';
?>

I have no idea what its talking about. Please give me a alternative way to do this!

can you please edit your code to include some more of your PHP code? if you're pasting that directly into phpMyAdmin's SQL command console, then it won't work because it strictly runs SQL only, not PHP. — Jeff Puckett, Jun 09 '16 at 02:32
@Rishi they are line numbers from an editor window copy/paste from the tutorial. — Jeff Puckett, Jun 09 '16 at 02:33
I actually added the numbers so you could see the lines it was talking about — Andrew C., Jun 09 '16 at 02:35
in order to check your SQL errors in PHP, change `if(!$result) echo 'Something went wrong...` to `if(!$result) echo mysql_errno($link) . ": " . mysql_error($link) . "\n";` for more details see http://php.net/manual/en/function.mysql-error.php — Jeff Puckett, Jun 09 '16 at 02:41
also, [Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – [Jay Blanchard](http://stackoverflow.com/users/1011527/jay-blanchard) — Jeff Puckett, Jun 09 '16 at 02:43

score 3 · Answer 1 · edited Jun 09 '16 at 07:28

3

You can't paste PHP into phpMyAdmin, phpMyAdmin works with SQL only.

You can test out the SQL with hard-coded data under phpMyAdmin, for example:

INSERT INTO
    users(user_name, user_pass, user_email ,user_date, user_level)
VALUES('admin',
    sha1('password'),
   'admin@example.com',
   NOW(),   
   0);

If you add an echo statement to display the contents of the $sql variable, you can take that value and paste it into phpMyAdmin.

Also, don't work with the mysql extension - it is deprecated in PHP 5.x and removed from PHP 7. Use mysqli or PDO instead.

edited Jun 09 '16 at 07:28

halfer

19,824
17
99
186

answered Jun 09 '16 at 02:34

user2182349

9,569
3
29
41

Thanks. You helped a lot! – Andrew C. Jun 09 '16 at 02:37

score 0 · Answer 2 · answered Jun 09 '16 at 02:42

SQL is spelling mistakes, the output debugged

$sql = "INSERT INTO 
    users(user_name, user_pass, user_email ,user_date, user_level) 
    VALUES('" . mysql_real_escape_string($_POST['user_name']) . "', 
    '" . sha1($_POST['user_pass']) . "', 
    '" . mysql_real_escape_string($_POST['user_email']) . "', 
    NOW(), 
    0)";

phpmyAdmin errors I don't understand

2 Answers2