HTTP Post to MySQL inserting Blank Rows

mysql insert into multiple rows
how to insert multiple rows in mysql using loop
mysql insert into select
how to insert multiple values in a single column in mysql
mysql insert multiple rows from select
how to insert data in mysql using php
mysql update
php mysql insert multiple rows at once

I'm trying to setup a post string to enter data directly into the database from a remote system.

Post string example: http://www.domainname.com/insert.php?Phone=07000888888

This returns my expected success message however when I check the database the phone field is blank.

My PHP insert script looks like:

<?php
$servername = "localhost";
$username = "XXXX";
$password = "XXXX";
$dbname = "Client1";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$phone = mysql_real_escape_string($_POST['phone']);

$sql = "INSERT INTO LiveFeed (phone)
VALUES ('$_POST[Phone]')";

if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Can anyone figure out why it's inserting a blank row with this?

There are a few problems with the code that as a server admin make me slightly uncomfortable. Hopefully we can fix that and your problem at the same time.

This is what I picked up on.

(I have removed empty lines for brevity)

<?php
   // ...
   $phone = mysql_real_escape_string($_POST['phone']);
   $sql = "INSERT INTO LiveFeed (phone)
   VALUES ('$_POST[Phone]')";
   // ...

The first thing is you are correctly escaping the string and then not using the escaped string

<?php
   // ...
   $phone = mysql_real_escape_string($_POST['phone']);
   $sql = "INSERT INTO LiveFeed (phone)
   VALUES ('$phone')";
   // ...

However you never inspect the value to see if you have anything.

<?php
   // ...
   if(isset($_POST['phone'])){
       $phone = mysql_real_escape_string($_POST['phone']);
       $sql = "INSERT INTO LiveFeed (phone)
       VALUES ('$phone')";
       // ...
   }else{
       echo '<p><b>Error:</b> "Phone" not given.</p>';
       exit(0); // stop and do no more
   }
   // ...

Finally (just in case): If you are sending the data this way http://example.com/example.php?phone=123456789 that is $_GET.

<?php
   // ...
   if(isset($_GET['phone'])){
       $phone = mysql_real_escape_string($_GET['phone']);
       $sql = "INSERT INTO LiveFeed (phone)
       VALUES ('$phone')";
       // ...
   }
   // ...

One way or another you should now have safe, clean and functional code that does not add empty rows and cannot so readily be abused.

MySQL INSERT, It means that MySQL generates a sequential integer whenever a row is inserted into the table. The start_date , due_date , and description columns use NULL as  INSERT Single Row 3. INSERT Default Values 3. INSERT Date Columns 4. INSERT Multiple Rows. Let’s now read and understand each of the section one by one. MySQL INSERT statement. As stated initially, the INSERT command is a built-in MySQL statement which inserts the specified records into a given table.

You code should be like this

<?php
$servername = "localhost";
$username = "XXXX";
$password = "XXXX";
$dbname = "Client1";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$phone = mysqli_real_escape_string($conn, $_GET['phone']);

$sql = "INSERT INTO LiveFeed (phone)
VALUES ('".$phone."')";

if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

MySQL INSERT INTO SELECT Explained By Practical Examples, This tutorial shows you how to use the MySQL INSERT INTO SELECT statement to In the previous tutorial, you learned how to insert one or more rows into a table INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone  2) MySQL INSERT – Inserting rows using default value example. If you want to insert a default value into a column, you have two ways: Ignore both the column name and value in the INSERT statement. Specify the column name in the INSERT INTO clause and use the DEFAULT keyword in the VALUES clause. The following example demonstrates the second way:

Without seeing your form, which would help I am assuming you are using GET not POST.

So you need to get your values with GET not POST if the following is the result of the form submission:

http://www.domainname.com/insert.php?Phone=07000888888

change:

$phone = mysql_real_escape_string($_POST['phone']);

$sql = "INSERT INTO LiveFeed (phone) VALUES ('$_POST[Phone]')";

to

$phone = mysql_real_escape_string($_GET['Phone']);

$sql = "INSERT INTO LiveFeed (phone) VALUES ('$phone')";

Additionally for some extra input validation you could do something like:

$phone = mysql_real_escape_string($_GET['Phone']);

if(!ctype_digit($phone)){
    echo "Incorrect Phone Number format";
}

$sql = "INSERT INTO LiveFeed (phone) VALUES ('$phone')";

That would make sure the phone number only has numbers in it (if that is indeed what you want and don't have +445498390 type numbers)

PHP MySQL Insert Data, The word NULL must not be quoted. The INSERT INTO statement is used to add new records to a MySQL table: INSERT INTO table_name (column1, column2,  MySQL issued the following message: 2 row(s) affected It means that two rows have been inserted into the projects table successfully. Note that when you insert multiple rows and use the LAST_INSERT_ID() function to get the last inserted id of an AUTO_INCREMENT column, you will get the id of the first inserted row only, not the id of the last

I finally figured it out.

The code itself was fine, it was the post string URL had an uppercase P in Phone.

once I changed it to phone it's now started working properly.

Thanks for all the help.

MySQL 8.0 Reference Manual :: 13.2.6 INSERT Statement, TABLE in MySQL 8.0.19 and later to insert rows from a single table. If both the column list and the VALUES list are empty, INSERT creates a row with each  RE: Query for Inserting multiple empty rows piti (TechnicalUser) 29 May 02 10:29 well, just limit the number of possible inserted rows to the max in one of your table, or create one table just for this purpose, u will not be forced to create it everytime user wants to insert rows

I was having this issue and it no matter what filtering I tried in PHP I could not stop the blank line from being inserted into my database records. What finally fixed the issue for me was adding .trim(); to my javascript variable. Hopefully, this will help someone else having similar issue!

MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ON , INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; With ON DUPLICATE KEY UPDATE , the affected-rows value per row is 1 if the row KEY UPDATE clause or INSERT statements and returns NULL otherwise. PHP Fail to insert data in MySQL, Inserts Blank Row instead. PHP. I know the code connects because it does insert a blank row after clicking submit. I know the form is providing the post

PHP Fail to insert data in MySQL, Inserts Blank Row instead, I know the code connects because it does insert a blank row after clicking submit. I have had it show me the $_post information after clicking  Insert Data Into MySQL Using MySQLi and PDO. After a database and a table have been created, we can start adding data in them. Here are some syntax rules to follow: The SQL query must be quoted in PHP; String values inside the SQL query must be quoted; Numeric values must not be quoted; The word NULL must not be quoted

Defining SQL Queries - Live Forms v7.3 Documentation, When its empty in your form, NULL is inserted fulfilling the MySQL Use the http.​post method to insert records into your database and use the  Insert a single row into a table; Insert multiple rows into a table; Copy rows from a table to another table. We will examine each function of the INSERT statement in the following sections. Insert one row into a table. To insert one row into a table, you use the following syntax of the INSERT statement. INSERT INTO table1 (column1, column2

MySQL INSERT statement, MySQL INSERT INTO statement is used to insert record(s) or row(s) into a If both the column list and the VALUES list are empty, INSERT creates a row <​meta http-equiv="Content-Type" content="text/html; charset=utf-8">  The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld , the affected-rows value is 1 (not 0) if an existing row is set to its

Comments
  • if the data comes from the query string, then you should look into $_GET, and mind the P and p of the index in Phone
  • And also this is vulnerable.
  • @AndriyStruk are you for real?
  • @php_nub_qq vulnerable from what? alien attacks? every body is.
  • I wonder how the op avoided all comments that provide useful information and focused on the one that doesn't :D
  • This one is also still only inserting a blank row.
  • Still entering a blank row for some reason.
  • Just to clarify - I added .trim() to my javascript variable that was being sent to PHP script and inserted into mysql database record. Adding the .trim() removed the blank line from from the beginning of my record.