Database cannot create a record when a text value is entered ($_POST)

Question

Perhaps I'm making some obvious beginner mistake, but I just cannot seem to figure out why this happens.

Strangely enough, the code only seems to work properly if I enter a number into the "inputbox". I check this in the myphpadmin panel, and it shows a new record has been created. However, if I attempt to input a string as intended for my purposes (example: "hello") no new record appears in the database...

In short, the database only updates if I put a number into the "inputbox" but not when I enter a string.

Any ideas why this may be happening? It's driving me crazy. If it helps, the data type of the "Company" field is VARCHAR and the collation is set to latin1_swedish_ci

The PHP code is as follows:

<?php


//Retrieve data from 'inputbox' textbox

if (isset($_POST['submitbutton']))
    {
    $comprating = $_POST['inputbox'];

    //Create connection

        $con = mysqli_connect("localhost","root","","test_db");

            if (mysqli_connect_errno())
                {
                    echo "Failed to connect to MySQL: " . mysqli_connect_error();
                }

    //Insert data into 'Ratings' table

    mysqli_query($con,"INSERT INTO Ratings (Company,Score)
    VALUES ($comprating,1)");

    mysqli_close($con);



    }


?>

The HTML code is:

<form method="post">

    <input type="text" name="inputbox">
    <input type="submit" name="submitbutton">

</form>

Cheers

Care for SQL/XSS injection and better use prepared statements — Daniel W., Feb 25 '14 at 16:21

Fallen · Accepted Answer · 2014-02-25T16:26:17.703

1

Try this query,

mysqli_query($con,"INSERT INTO Ratings (Company,Score)
VALUES ('$comprating',1)");`
        ^           ^

Note the single quotes that reserves the string value and don't forget to sanitize the input before inserting them to database.

Sample standard escaping:

$comprating = mysqli_real_escape_string($comprating) before executing a query that uses $comprating

edited Feb 25 '14 at 16:26

answered Feb 25 '14 at 16:18

Fallen

4,435
2
26
46

That should do it but `$comprating = $_POST['inputbox'];` really, really needs sanitising first : https://xkcd.com/327/ – CD001 Feb 25 '14 at 16:20
@CD001: Thanks, I was adding that on extended answer :) – Fallen Feb 25 '14 at 16:21
Thanks for the help. Worked like a charm!! I wished I asked this earlier :D – user3352084 Feb 25 '14 at 16:29

score 0 · Answer 2 · answered Feb 25 '14 at 16:49

Hi here is the objected oriented method and also its secure because data binding is used in mysqli. I recommend to use this.

if (isset($_POST['submitbutton'])) {

$comprating = $_POST['inputbox'];
$mysqli = new mysqli("localhost", "root", "", "test_db");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$stmt = $mysqli->prepare("INSERT INTO Ratings (Company,Score) VALUES (?, ?)");
$stmt->bind_param($comprating, 1);

/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$mysqli->close();
}

feel free to ask any questions if you have..

Database cannot create a record when a text value is entered ($_POST)

2 Answers2