PHP only adding Numbers to sql in column of VARCHAR

Question

PHP only adding Numbers to MySQL in column of VARCHAR instead of texts when using query directly in MySQL it works...but if I use $_POST from HTML, IT fails I don't know the reason how it is getting failed. what is the problem here ?

<?php 
    $link=mysqli_connect("localhost","root","","home_ac");
    if(mysqli_connect_error()) {
        die("error in database");
    }

    $name =$_POST["name"];
    $query = "INSERT INTO `test`(`number`, `name`) VALUES (NULL,$name)";

    if(mysqli_query($link, $query)){
        echo "done";
    }
    else {
        echo "failed";
    }   
?>

<!doctype html>
<html>
    <head>
    <meta charset="utf-8">
        <title>Untitled Document</title>
    </head>

    <body>
        <form method="post">
            <input type="text" placeholder="enter a name" name="name">
            <input type="submit" value="add">
        </form>
    </body>
</html>

MySQLi is perfectly valid, you don't need to change to PDO unless there's another reason than someone saying "I like it better". MySQLi offers prepared statements too. — Qirel, Oct 01 '17 at 09:48
Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Qirel, Oct 01 '17 at 09:49
@Qirel For Your Infomation `PDO` Support 12 databases and MYsqli only support MySQL — RïshïKêsh Kümar, Oct 01 '17 at 09:51
...which doesn't matter, because the question is about mysql. And it's extremely unlikely that someone changes their database engine out of the blue. All that matters is that one uses prepared statements, be it through MySQLi or PDO, it makes little difference in the end (the methods are just different). — Qirel, Oct 01 '17 at 09:56

score 1 · Accepted Answer · edited Oct 03 '17 at 12:35

1

You need quotes around text

$query = "INSERT INTO `test`(`number`, `name`) VALUES (NULL,'$name')";

Please, think about prepared query. It solve quotes problem and protect from SQL injection.

edited Oct 03 '17 at 12:35

marc_s

732,580
175
1,330
1,459

answered Oct 01 '17 at 09:31

Ivan Bolnikh

742
9
19

score 0 · Answer 2 · edited Oct 01 '17 at 10:03

You have to use PHP Prepared Statements or PHP Data Objects (PDO).

For example, using PDO:

<html>
    <head>
    <meta charset="utf-8">
    <title> Example PDO Insert </title>
    </head>

    <body>
    <form method="post" action="" name="myForm" id="myForm">

        <input type="text" placeholder="Enter Your Name" name="name" required="required">
        <input type="submit" name="submit" value="add">
    </form>
    </body>
</html>




<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "home_ac";

try {

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    if ( isset($_POST['submit']) && !empty($_POST['name']) ) {
        # code...

    $sql = "INSERT INTO test (number,name) VALUES (NULL,'$name')";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "New record created successfully";

    }

}
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

No, you don't have to use PDO. It's a matter of preference. Prepared statements, I agree, but MySQLi offers it too. And your code is still vulnerable to sql-injection... You still don't use prepared statements — Qirel, Oct 01 '17 at 09:50
@Qirel For Your Infomation `PDO` Support 12 databases and MYsqli only support MySQL — RïshïKêsh Kümar, Oct 01 '17 at 09:51
Yes, I'm aware - but so what? The question is about mysql, so the other database engines doesn't matter! — Qirel, Oct 01 '17 at 09:52

PHP only adding Numbers to sql in column of VARCHAR

2 Answers2