SQL query inserting twice

Question

I'm creating a quiz page that would ask questions and at the end will show top scores table. I'm accessing this page through Ajax to insert username and score and it's inserting it twice.

<?php
    $servername = "localhost";
    $username = "root";
    $password = "pswd";
    $dbname = "mydb";
    $toJsonArr = array();

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    if (isset($_GET["username"])) {
        $username = $_GET["username"];
        $score = $_GET["score"];

        $sql = "INSERT INTO `fullstackQuiz` (`id`, `place`, `username`, `points`, `now`) VALUES (NULL, '0', '$username', '$score', CURRENT_TIMESTAMP);";
        $result = $conn->query($sql);

        if ($conn->query($sql) === TRUE) {
            echo "1";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }

        $conn->close();

    } else {
        $sql = "SELECT * FROM `fullstackQuiz` ORDER BY `fullstackQuiz`.`points` DESC LIMIT 10";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                  $toJsonArr[] = $row;
            }
        } else {
            echo "0 results";
        }

        echo json_encode($toJsonArr);
        $conn->close();
    }
?>

My Ajax code:

$.ajax({
  type: "GET",
  url: "sql.php",
  data: { username: "abc", score: "99" },
  success: function(data) {
    console.log("success");
  }
});

Every time the Ajax runs, it will create the record twice for some reason.

[Little Bobby](http://bobby-tables.com/) says [**your script is at risk for SQL Injection Attacks**](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — rollstuhlfahrer, Feb 03 '18 at 16:47

score 3 · Accepted Answer · answered Feb 03 '18 at 15:21

You repeated $conn->query($sql) twice. Remove one and it will work:

//$result = $conn->query($sql); <== Remove this line.

if ($conn->query($sql) === true) {
    echo "1";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Also don't pass NULL to an auto-increment ID, just exclude it from the query. And if the place field is a number, then pass the value as a number without apostrophes:

$sql = "INSERT INTO `fullstackQuiz` (`place`, `username`, `points`, `now`)
        VALUES (0, '$username', '$score', CURRENT_TIMESTAMP);";

Finally, your query is open to SQL attacks. Use a parameterized query instead.

SQL query inserting twice

1 Answers1