duan0714
2015-01-21 23:26 浏览 35
已采纳

MySQL从PHP运行时显示SQL语法错误

I am trying to create a dynamic page and store it in a MySQL database. It connects to the database fine, but there seems to be an error in the SQL Syntax that I can't find. I've tried reformatting the code and cannot pin point it. Here's the PHP:

<!DOCTYPE html>
<html>
    <head lang="en">
        <meta charset="UTF-8">
        <title></title>
    </head>
    <body>
        <?php
            $dbc = mysqli_connect('localhost', 'username', 'password', 'test_db')
            or die("There was an error connecting to the database. Please try again later.");

            $fullname = (string)$_POST['name'];
            $guest_email = $_POST['email'];
            $password = $_POST['password'];
            echo "<h1>Thanks for your submission!</h1>";
            echo "Your Name on File is: ". $fullname . '<br>';
            echo "Your Email on File is: ". $guest_email . '<br>';
            echo "Your Password on File is: ". $password . '<br>';

            $add_query = "INSERT INTO test_form (full_name, email, user_pass) VALUES( $fullname, $guest_email, $password)";

            $result = mysqli_query($dbc, $add_query)
            or die("<strong>There was an error processing the form. Please call your IT support!</strong>". mysqli_error($dbc));

            mysqli_close($dbc);
        ?>
    </body>
</html>

And here's the HTML:

<!DOCTYPE html>
<html>
    <head lang="en">
        <meta charset="UTF-8">
        <title>Test Form</title>
    </head>
    <body>
        <h1>Test Form</h1>
        <form action="index.php" method="post">
            <input type="text" placeholder="Name" name="name"/>
            <input type="email" name="email" id="email" placeholder="Email"/>
            <input type="password" name="password" id="password" placeholder="Enter a password"/>
            <input type="submit" value="Submit"/>
        </form>
    </body>
</html>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答 默认 最新

  • 已采纳
    dongzhuo8210 dongzhuo8210 2015-01-21 23:53

    You need to quote your values when it comes to strings

    ('$fullname', '$guest_email', '$password')
    

    Important note about password storage:

    I noticed you may be storing passwords in plain text. If this is the case, it is highly discouraged and if used on a live site, you will get hacked.

    I recommend you use CRYPT_BLOWFISH or PHP 5.5's password_hash() function. For PHP < 5.5 use the password_hash() compatibility pack.

    If and when you do use one of those, make sure the column is long enough to accomodate the hash.

    Plus, in regards to SQL injection which you are presently open to, use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

    点赞 评论 复制链接分享
  • dtsc14683 dtsc14683 2015-01-21 23:30

    In $add_query, you're not concatenating the other variables correctly, like you did before. This should work :

    $add_query = "INSERT INTO test_form (full_name, email, user_pass) VALUES ( '" . $fullname . "' , '" . $guest_email . "' , '" . $password . "')";
    
    点赞 评论 复制链接分享
  • dongping9475 dongping9475 2015-01-22 00:42

    You should be aware of the fact that your code is vulnerable to SQL Injection, so it must not be used in 'real world' environments but for testing only!

    To prevent SQL Injections from the beginning you can use Prepared Statements as described in the docs:

    I am not familar with the mysqli driver and would recommend to use PDO, which is a more generic approach for database connection.

    However, your code with mysqli prepared statements should look like this:

    <?php
    $dbc = mysqli_connect('localhost', 'username', 'password', 'test_db')
    or die("There was an error connecting to the database. Please try again later.");
    
    $fullname = (string)$_POST['name'];
    $guest_email = $_POST['email'];
    $password = $_POST['password'];
    echo "<h1>Thanks for your submission!</h1>";
    echo "Your Name on File is: ". $fullname . '<br>';
    echo "Your Email on File is: ". $guest_email . '<br>';
    //echo "Your Password on File is: ". $password . '<br>'; // never ever print any password!
    
    /* create prepared statement */
    $stmt = mysqli_prepare($dbc, "INSERT INTO test_form (full_name, email, user_pass) VALUES(?, ?, ?)");
    
    /* bind params to prepared statement */
    mysqli_stmt_bind_param($stmt, 'sss', $fullname, $guest_email, $password);
    
    /* execute prepared statement */
    mysqli_stmt_execute($stmt);
    
    /* close statement and connection */
    mysqli_stmt_close($stmt);
    
    /* close connection */
    mysqli_close($dbc);
    

    ?>

    点赞 评论 复制链接分享

相关推荐