variable not inserted to table

Question

I have a very simple code to insert data into database, also checks if already has same data in table

[HTML]

<html>
<head>
    <title>nickname</title>
</head>
<body>
    <p>input nickname</p>
    <form action="foo.php" method="post">
        <input type="text" name="nickname" />
        <input type="submit">
    </form>
</body>
</html>

[PHP]

//foo.php
if(isset($_POST['nickname']) && !empty($_POST['nickname'])) {
    $nickname = $_POST['nickname'];

    //Object Oriented way
    $servername = "localhost";
    $username = "root";
    $password = "foo_bar";
    $dbname = "nickname";

    //check connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    if($conn->connect_error) {
        die("cannot connect:".$conn->connect_error);
    }

    $sql = "SELECT * FROM nickname WHERE nickname='$nickname'";
    $result = $conn->query($sql);

    if($result->num_rows > 0) {
        echo "nickname exists!";
    } else {
        $sql = "INSERT INTO nickname(nickname) VALUES(".$nickname.")";
        $conn->query($sql);
        echo "welcome $nickname!";
    }

    $conn->close();

} else {
    echo "please input your nickname";
}

I am still learning how to use MySQL, what am I doing wrong? especially this line

$sql = "INSERT INTO nickname(nickname) VALUES(".$nickname.")";
$conn->query($sql);

the data in the variable is not inserted to table, nothing happens. but if I replace $nickname to plain text the data is inserted.

edit
why is this a duplicate? the question is not even about "how to prevent sql injection"

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Feb 13 '17 at 10:20
@RiggsFolly thanks for the reply, yes I have to use prepared statements, but does that solve the problem? I wrote a simple script just to test... — ha_ryu, Feb 13 '17 at 10:23
@ha_ryu quite contrary, a prepared statement is the very and the only solution for this problem. Which means that a duplicate link is actually wrong. — Your Common Sense, Feb 13 '17 at 10:29
His comment is right but the linked answer is obviously wrong. — Your Common Sense, Feb 13 '17 at 10:33
@YourCommonSense you're always closing questions like this with that duplicate. You need to use the right one [When to use single quotes, double quotes, and backticks in MySQL](http://stackoverflow.com/q/11321491/1415724) - Just like the OP stated in the question edit: *"why is this a duplicate? the question is not even about "how to prevent sql injection""* — Funk Forty Niner, Feb 13 '17 at 13:04

score 1 · Accepted Answer · answered Feb 13 '17 at 10:23

1

Give single quotes for the variable

$sql = "INSERT INTO nickname(nickname) VALUES('".$nickname."')";
$conn->query($sql);

answered Feb 13 '17 at 10:23

affaz

1,191
9
23

thanks! I was doing it the wrong way! VALUES(" '.$nickname.' "), but aren't there any problem with my code if I query twice? or more? – ha_ryu Feb 13 '17 at 10:25
Yes..Welcome.Good luck :) – affaz Feb 13 '17 at 10:27
this answer is wrong – Your Common Sense Feb 13 '17 at 10:28
@YourCommonSense Why? – affaz Feb 13 '17 at 10:29
@YourCommonSense please I need your explanation. (The only proper) PDO tutorial Ill take a look at this page, but I want to learn first in MySQLi object oriented way. – ha_ryu Feb 13 '17 at 10:30
@ha_ryu Did the data get inserted to the table now? – affaz Feb 13 '17 at 10:31
@ha_ryu there are links in the Riggs' comment. It looks like you didn't check any of them. It means you don't have any actual interest at all. – Your Common Sense Feb 13 '17 at 10:32
@ha_ryu yep data got inserted thnks. – ha_ryu Feb 13 '17 at 10:35
@ha_ryu so mark it as solved then. – Funk Forty Niner Feb 13 '17 at 13:07
@Fred-ii- yeah thanks and thanks affaz – ha_ryu Feb 14 '17 at 00:58
you're welcome @ha_ryu – Funk Forty Niner Feb 14 '17 at 00:58
I cannot still understand why I have to use prepared statement for this problem to be solved. it was just simple single quotes. using prep statement is another problem, its for the security I understand that. – ha_ryu Feb 14 '17 at 01:03
@ha_ryu yes..you are right – affaz Feb 14 '17 at 05:35

variable not inserted to table

1 Answers1