MYSQL data insert query is not working

Question

I want to insert some data from a form to a database table "sumation". But it's not working. I use PhpStorm IDE and it's shows no data sources are configured to run this sql and sql dialect is not configured. Where is the problem ?

<?php
    $db= new PDO('mysql:host=localhost;dbname=test;cahrset=utf8','root','');
    if(isset($_POST['submit'])){
        $id=$_POST['id'];
        $first=$_POST['first'];
        $second=$_POST['second'];
        $third=$_POST['third'];

        $sql="INSERT INTO sumation VALUES($id,'$first','$second','$third')";
        $db->query($sql);
        echo("<script>alert('Data Inserted Sucessfully !')</script>");
    }

?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
</head>
<body>
    <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
        ID: <input type="text" name="id"><br>
        First: <input type="text" name="first"><br>
        Second: <input type="text" name="second"><br>
        Third: <input type="text" name="third"><br>
        <button type="submit" class="btn-primary" name="submit">Insert </button>
    </form>

</body>
</html>

I also suggest escaping dangerous characters in the user input or you will be vulnerable to SQL injection attacks. — Parsa, Nov 26 '15 at 15:03
No errors. Even the javascript code inside the echo is working. But it don't insert any data in database table. — Utpal Paul, Nov 26 '15 at 15:05
cahrset, you probably meant charset, injection here are pretty at risk.. AND turn on error reporting to report error? — Mark Ng, Nov 26 '15 at 15:05
http://php.net/manual/en/pdo.query.php get you started on some basics to avoid sql injection — Mark Ng, Nov 26 '15 at 15:15

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Your query is wrong, the syntax of INSERT is

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

So your query would look like

INSERT INTO sumation (id, first, second, third) VALUES ($id, '$first', '$second', '$third')

You also just assume that your query is executed. A PDO query would return an object on success, and boolean false on failure, meaning that you could wrap it into an if-statement.

You should also read up on How can I prevent SQL-injection in PHP?, which basically means that you should use prepared statements.

score 0 · Answer 2 · answered Nov 26 '15 at 15:08

0

Please try

$sql="INSERT INTO sumation VALUES($id,'$first','$second','$third')";

Just replace

$sql="INSERT INTO sumation (id,first,second,third) VALUES ($id,'$first','$second','$third')";

answered Nov 26 '15 at 15:08

fool-dev

6,960
8
32
49

score 0 · Answer 3 · answered Nov 26 '15 at 15:10

This should work:

<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {

    $id=$_POST['id'];
    $first=$_POST['first'];
    $second=$_POST['second'];
    $third=$_POST['third'];

    $conn = new mysqli('localhost', 'root', '', 'test');
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql="INSERT INTO sumation (id,first,second,third) VALUES ($id,'$first','$second','$third')";

    if ($conn->query($sql) === TRUE) {
        echo("<script>alert('Data Inserted Sucessfully !')</script>");
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
?>

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
</head>
<body>
<form method="post">
    ID: <input type="text" name="id"><br>
    First: <input type="text" name="first"><br>
    Second: <input type="text" name="second"><br>
    Third: <input type="text" name="third"><br>
    <button type="submit" class="btn-primary" name="submit">Insert </button>
</form>

</body>
</html>

score 0 · Answer 4 · answered Nov 26 '15 at 15:13

To properly answer your question on how to protect your application from SQL injection attacks.

An SQL injection attack is where a user inserts SQL commands into their input string allowing them to run SQL queries on your database. This means they can drop the whole database or print out all the rows.

You can use the PDO quote function.

$id=$db->quote($_POST['id']);
$first=$db->quote($_POST['first']);
$second=$db->quote($_POST['second']);
$third=$db->quote($_POST['third']);

Alternatively I would recommend you use PDO prepare and execute functions read documentation here: http://php.net/manual/en/pdo.prepare.php

MYSQL data insert query is not working

4 Answers4