向不工作的数据库插入数据(PHP-SQL)

My insert data sql command in my PHP code is not working. Can anyone help please?

在PHP代码中插入数据sql命令无效。谁能帮助好吗?

I have a registration form that takes the values from the input fields (name, lastname, email, username and password)

我有一个注册表单，它从输入字段中获取值(名称、姓、电子邮件、用户名和密码)

The values that the user inputs in this fields should be saved into my table "users" whith columns (ID [which is the primary key /INT], name [TEXT], lastname[TEXT], e-mail [VARCHAR], username[VARCHAR] and password [VARCHAR]) .

用户在此字段中输入的值应该保存到我的“users”表中(ID[是主键/INT]， name[TEXT]， lastname[TEXT]， e-mail [VARCHAR]， username[VARCHAR]和password [VARCHAR])。

Here is my current code:

这是我现在的代码:

if (isset ($_POST['name'],$_POST['lastname'],$_POST['email'],$_POST['username'], $_POST['password']))
    {
        //connect to database
        $conn = mysqli_connect ('localhost', 'root', '', 'test_database');

        if ($conn)          
        {
            $sql="SELECT username FROM users WHERE username = '" . $_POST['username'] . "';";
            $query = mysqli_query ($conn, $sql);
            $result = mysqli_fetch_array ($query);

            if ($result ['username'])
            {
                header ('Location: ' . $_SERVER['PHP_SELF'] . '?errno=1');
            }
            else
            {
                $sql="INSERT INTO users (ID, name, lastname, e-mail, username, password) VALUES (' ','" .$_POST['name'] . "' ,'" . $_POST['lastname']. "' ,'" . $_POST['email']. "' ,'" . $_POST['username']. "' ,'" . $_POST['password']. "');";
                mysqli_query ($conn, $sql);
                mysqli_close ($conn);   
                //registration completed, redirect to index page
                //header ('Location:index.php?reg=1');

            }
        }

        else
        {
            echo 'connection error';
        }

    }

1 个解决方案

#1

Besides what has already been outlined in comments for the space VALUES (' ', for the ID column etc., your email column name contains a hyphen and is interpreted as e MINUS mail and as a mathematical operation.

除了在空间值(' '，ID列等)注释中已经概述的内容之外，您的电子邮件列名称包含一个连字符，并被解释为e - mail和数学运算。

Either rename it to e_mail or place ticks around it.

要么将其重命名为e_mail，要么在其周围放置标记。

`e-mail`

Read the following on Identifier Qualifiers:

阅读以下标识符限定符:

http://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html
http://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html

Having used mysqli_error($conn) on the query would have thrown you a syntax error.

在查询中使用mysqli_error($conn)将导致语法错误。

Sidenote: You should be escaping your data for quite a few reasons, one is for protection against an SQL injection and if your data could contain characters that MySQL could complain about such as John's Bar & Grill as an example.

Sidenote:由于许多原因，您应该转义数据，其中之一是为了防止SQL注入，以及如果您的数据可能包含MySQL可能会抱怨的字符，例如John's Bar & Grill。

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements.

您当前的代码对SQL注入是开放的。使用准备好的语句，或使用准备好的语句的PDO。

Passwords

密码

I also noticed that you may be storing passwords in plain text. This is not recommended.

我还注意到您可能正在用纯文本存储密码。这是不推荐。

Use one of the following:

使用下列其中之一:

CRYPT_BLOWFISH
CRYPT_BLOWFISH
crypt()
crypt()
bcrypt()
bcrypt()
scrypt()
scrypt()
On OPENWALL
在OPENWALL
PBKDF2
PBKDF2
PBKDF2 on PHP.net
在PHP.net上PBKDF2
PHP 5.5's password_hash() function.
PHP 5.5的password_hash()函数。
Compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/
兼容性包(如果PHP < 5.5) https://github.com/ircmaxell/password_compat/

#1