MySQL insert error in PHP

Question

I've been trying to get the code below to execute, although no error appears, the row is not inserted when I check the result in the Database. There seems to be a problem in passing $query into $sql, because when I echo the same value of $query and add it as text into $sql it works fine.

any idea what is the error ?

$i=3;

$query = '"'."INSERT INTO data (ID,QN,QI,CB,BK,QK)".""."VALUES("."'".$i."','".$price[17]."','".$price[47]."','".$price[77]."','".$price[107]."','".$price[137]."')".'"';

echo $query;

$connect= mysql_connect("localhost", "urs" , "password") or die(mysql_error());

mysql_select_db("data", $connect);

$sql= $query; 

mysql_query($sql,$connect)

`'"'."` what is dat?! if you tried to enter `"` symbol, you should do it this way: "\"INSERT....\"";. Also there is no need for it in this case. Could be simply "INSERT...."; — Ed T., May 31 '14 at 23:26
You need to read up on [proper SQL escaping](http://bobby-tables.com/php) so you don’t create any more severe [SQL injection bugs](http://bobby-tables.com/) like the one you have here. Also, `mysql_query` should not be used in new applications. It's a deprecated interface that's being removed from future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and is a safer way to compose queries. What you're doing here is highly error prone and very hard to do correctly. — tadman, May 31 '14 at 23:28
@EdT. The concatenation is so specific it’s a piece of art. — Giacomo1968, May 31 '14 at 23:32
**Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. **Running SQL statements built with outside data is like eating soup made from ingredients found on your doorstep.** — Andy Lester, Jun 01 '14 at 01:40

Giacomo1968 · Accepted Answer · 2014-05-31T23:52:13.890

The problem is your query has no space right before VALUES which would make the query syntactically incorrect as far as MySQL goes. It would render like this when the script runs:

INSERT INTO data (ID,QN,QI,CB,BK,QK)VALUES(…);

MySQL would choke on that query. Instead, try this version of your query with one space added before the VALUES:

$query = '"'."INSERT INTO data (ID,QN,QI,CB,BK,QK)".""." VALUES("."'".$i."','".$price[17]."','".$price[47]."','".$price[77]."','".$price[107]."','".$price[137]."')".'"';

But that said, your concatenation makes 100% no sense & is unnecessarily complex. I’ve cleaned that up & this should work:

$query = "INSERT INTO data (ID,QN,QI,CB,BK,QK) VALUES('$i','$price[17]','$price[47]','$price[77]','$price[107]','$price[137]');";

The key you need to understand is double quotes in PHP allow for string substation. So no need to concatenate to the degree you did.

Also you are missing a semi-colon after the mysql_query() so it should be like this:

mysql_query($sql,$connect);

So here is my cleaned up version of your code:

// Set the value for `$1`.
$i = 3;

// The query.
$query = "INSERT INTO data (ID,QN,QI,CB,BK,QK) VALUES('$i','$price[17]','$price[47]','$price[77]','$price[107]','$price[137]');";

// Set the connection or die returning an error.
$connect = mysql_connect("localhost", "urs" , "password") or die(mysql_error());

// Select the database;
mysql_select_db("data", $connect);

// Run the query or die returning an error.
mysql_query($query, $connect) or die(mysql_error());

But now really looking at it you are selecting a database named data and then inserting into a table within that database called data as well? Is that correct? Seems like you might have mixed up the idea of a database & a table so double check that.

Also note that I added an or die(mysql_error()); after mysql_query($query, $connect). Your first or die(mysql_error()); only covers errors in the connection & not errors that would appear when the query runs. Now if the query has an error, it will die with an error being returned.

That said, you should explore using mysqli_* commands instead of mysql_* since mysql_* commands are depreciated in PHP 5.3 to 5.4 and are completely eliminated in PHP 5.5.

Thank you <3 that made it work .. I can see clearly what I got wrong — AlAnoodZ, May 31 '14 at 23:45
@user3689319 You’re welcome. Made one final addition by adding an `or die(mysql_error());` after `mysql_query($query, $connect)`. That would show you query errors. Look at my latest edit where I mention, or `die(mysql_error());`. — Giacomo1968, May 31 '14 at 23:51

MySQL insert error in PHP

1 Answers1