problems inserting $_POST into mysql

slightlyeskew

I'm trying to insert data from a $_POST into mysql.

Initially I tried

<?php

...

$InsertStatement = "INSERT INTO test VALUES ('$POST[x]', '$POST[y]', '$_POST[z]')";

//this compiles, but does not put values into the test table.
//assume $db connection

$Insertion = $db->query($InsertStatement);

I was extensively warned that I should not use $_POST inline because of potential SQL injection. So I tried to set them to variables and then put them in the SQL statement.

<?php
...
$x = $POST[x];
$y = $POST[y];
$z = $_POST[z];

$InsertStatement = "INSERT INTO test VALUES ('$x, 'y', 'z')";

$Insertion = $db->query($InsertStatement);

?>
This also compiles, but does not insert values into the table :queasy: . As always, any help would be greatly appreciated!

etully

My typical syntax is:

insert into tablename (field1,field2,field3) values ('$x','$y','$z');

I think you can do it your way IF you have exactly three fields. Does your table have more than 3 fields? If that's not the problem, then try my syntax instead.

By the way, it's good that you were warned about SQL injection but I think the person taught you half of what you need to know. Just converting the $_POST to variables does nothing. It's a good start but until you validate and encode the values, you're not protected. Your script is still 100% vulnerable. You need to learn how SQL injection works in order to defend against it correctly.

slightlyeskew

Thanks for the quick response etully.

Unfortunately that way isn't working. I actually edited my original code for a quicker read, but essentially I do have 3 fields, for the 3 variables. When I load the page, it loads without an error, but no update is made in any of the fields in the table.

Do you have any other suggestions? Usually my problems relate to not including appropriate single quotes around fields.

Right now I am using the second method, where I set the $_POST to a variable and then in the insert statement have something along these lines:

$x = $POST[x];
$y = $POST[y];
$z = $_POST[z];

INSERT INTO tablename (field1, field2, field3) VALUES ('$x', '$y', '$z');

Thanks again for the quick response!

dagon

simply assigning the value to a new var offers no protection, at the least you should use
mysql_real_escape_string()

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

slightlyeskew

Thanks for the suggestion on preventing the SQL injection Dagon. I tried using the function in the script but it didn't clear up the problem with the insertion into mysql. The script still runs fine, but it will not insert the data into the table.

Any other suggestions? Thanks so much for your time.

slightlyeskew

At the top of the script, I perform a query on one table to get the size of an array. This query works fine and I can echo data from that table. Further down, I make a query to a different table, which does not execute.

Do I need to somehow close the initial connection before starting a new one?
Thanks again!

etully

First of all, Dagon's recommendation that you use mysql_real_escape_string() wasn't designed to fix your problem. He just mentioned it because it gets you closer to preventing SQL injection. (It doesn't fix the problem completely, but it gets you closer - you still need to investigate SQL injection).

Second of all, to fix your problem, what you need now is a print statement. After you set your query ($InsertStatement) print it out like this:

print "The query I'm trying to run is:<br>";
print "$InsertStatement<p>";

This way, you can see the SQL statement and see if there's anything wrong that would prevent the insert from happening. If it looks right, copy and paste it into a MySQL command line or into PHPMyAdmin or whatever direct access you have to MySQL. That will give you an error message that will explain why your insert query isn't working.

slightlyeskew

I tried your suggestion etully:

etully;10928559 wrote:
=
print "The query I'm trying to run is:<br>";
print "$InsertStatement<p>";

...the result came out about how I was expecting it:

INSERT INTO testsharepricehistory (PriceID, PriceDate, Earnings_Factor, Revenue_Factor, Book_Value_Factor) VALUES ('47', '2009-09-21', '5', '5', '5')

I next set a if PEAR::isError($test) { die('message' $test->getMessage())

I received:

Fatal Error: Call to undefined method MDB2_BufferedResult_mysql::getMessage()

Which seems to say to me, unfortunately, that the function I used to get the error message from mysql about the error that was occurring in fact generated an error hahaha.

Anyway, as always thanks for all the help. Any suggestions on how to fix this would be appreciated.

etully

As I already wrote,

If it looks right, copy and paste it into a MySQL command line or into PHPMyAdmin or whatever direct access you have to MySQL. That will give you an error message that will explain why your insert query isn't working.

slightlyeskew

Stupid mistake on my part:

slightlyeskew;10928563 wrote:
I next set a if PEAR::isError($test) { die('message' $test->getMessage())

I received:

Fatal Error: Call to undefined method MDB2_BufferedResult_mysql::getMessage()

Which seems to say to me, unfortunately, that the function I used to get the error message from mysql about the error that was occurring in fact generated an error hahaha.

I had fixed the getmessage() error [I had used PEAR::isError($x) {die('message' $y ->getMessage())

The error I get now is:

Failed to issue query, error message : MDB2 Error: unknown error.

This error seems more frightening.

slightlyeskew

Sorry I didn't directly respond to that first point etully. The post into mysql works fine with the insert statement I received from print as you suggested.

slightlyeskew

RESOLVED:

I ended up debugging it...too many single quotes in this place and that.

On another note, thanks again for the advice on the SQL injection guys!