无法在 MySQL 数据库中插入表单数据 [英] Unable to insert form data in MySQL database

问题描述

我正在使用以下 php 脚本在我的 MySQL 数据库中插入数据.但是没有插入任何行..当我在 phpmyadmin 中运行相同的 sql 查询时，它工作正常.

I am using following php script to insert data in my MySQL database.But no rows are getting inserted..when I run same sql query in phpmyadmin its working fine.

<?php

if(isset($_POST['submit']) && isset($_POST['Pnr']) && isset($_POST['Mobile']))

{
    $hostname = "";
    $username = "";
    $password = "";
    $Pnr = $_POST['Pnr'];
    $Mobile = $_POST['Mobile'];

    $con = mysql_connect($hostname,$username,$password);

if(!$con)
{
    die("Could not connect to Database");
}

    mysql_select_db("freepnra_userinfo",$con);

    $sql = "INSERT INTO users (pnr,mobile) VALUES ('$Pnr','$Mobile')";

    mysql_query($sql) or die("insertion error");
}

?>  

推荐答案

我不完全确定它不起作用的实际原因是什么，但我怀疑 SQL 查询嵌套或已弃用的 MySQL 数据库扩展的问题.

I am not completely sure what was the actual reason why it didn't work, but I suspect either SQL query nesting or issues with deprecated MySQL database extension.

我测试了以下代码，对我来说似乎完美无缺.

I tested the following code and seems to be working flawlessly to me.

<?php
    if (isset($_POST['submit']) && isset($_POST['Pnr']) && isset($_POST['Mobile'])) {
        $hostname = "";
        $username = "";
        $password = "";
        $database = "freepnra_userinfo";
        $Pnr = $_POST['Pnr'];
        $Mobile = $_POST['Mobile'];

        $con = mysqli_connect($hostname, $username, $password, $database);

        if (mysqli_connect_errno()) {
            die("<p>Could not connect to database with the credentials passed in (" . mysqli_connect_errno() . ": " . mysqli_connect_error() . ").");
            exit(1);
        }

        $Pnr = preg_replace("/[^0-9-]/", "", $Pnr);
        $Mobile = preg_replace("/[^0-9-]/", "", $Mobile);
        $sql = "INSERT INTO `users` (`pnr`, `mobile`) VALUES (?, ?)";

        if ($stmt = mysqli_prepare($con, $sql)) {
            mysqli_stmt_bind_param($stmt, "ss", $Pnr, $Mobile);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);
        }

        mysqli_close($con);
    }
?>

我所做的是嵌套表名、列名，而不是将变量值直接放入查询中，而是放置了双撇号.

What I did, was that I nested the table name, the column names and instead of placing the variable values straight into the query, I placed double apostrophes.

我也改用了 MySQLi 数据库扩展不推荐使用的 MySQL.另外，我想警告你一个非常严重的SQL 注入 问题，我通过使用准备好的语句和一些额外的 preg_replace 函数解决了这个问题，只是为了将所有不需要的字符排除在查询和表之外.我不完全确定这些值应该包含什么，所以我继续假设你想要两个不同的电话号码(这就是为什么我只允许从 0 到 9 和破折号的数字).

I also switched to MySQLi database extension instead of your deprecated MySQL. In addition, I'd like to warn you of a very serious SQL injection problem and I fixed that issue by using prepared statements and some extra preg_replace function just to keep all unwanted characters out of the query and table in general. I am not entirely certain of what those values should contain, so I went on assuming you want two different phone numbers (which is why I went for only allowing numbers from 0 to 9 and dashes).

此外，我想建议您阅读以下材料，以了解一些更好的方法来保护您的查询免受潜在的 SQL 注入:

Additionally, I would like to refer you to read the following material to get to know some better ways of defending your queries against potential SQL injections:

• stackoverflow.com - 如何防止 PHP 中的 SQL 注入
• php.net - SQL 注入

如果可能的话，我强烈建议切换到 MySQL PDO.在我看来，它非常简单、容易并且效果更好！

I highly recommend switching to MySQL PDO if just possible. It's very simple, easy and works a lot better in my opinion!

希望这对您有所帮助！

这篇关于无法在 MySQL 数据库中插入表单数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！