Help with an INSERT

  • confuzzled1234
  • Born
  • Born
  • confuzzled1234
  • Posts: 1

Post 3+ Months Ago

Hi all,

Im new to PHP and MySQL and have been stumbling through bits and pieces. Im not stuck on what i think is a
stupid problem that i cant find the fault of. All im trying to do is insert some details to my database.

PHP Code: [ Select ]
$product = $_POST['Product'];
        $description = $_POST['Description'];
        $itemwanted = $_POST['Itemwanted'];
        $query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES (". $product . "," . $_SESSION['ID'] . ",". $description . ",". $target_path . ",". $itemwanted . ")";
        echo "<br></br>";
        mysql_query($query,$link) or die("Insertion Failed:" . mysql_error());
  1. $product = $_POST['Product'];
  2.         $description = $_POST['Description'];
  3.         $itemwanted = $_POST['Itemwanted'];
  4.         $query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES (". $product . "," . $_SESSION['ID'] . ",". $description . ",". $target_path . ",". $itemwanted . ")";
  5.         echo "<br></br>";
  6.         mysql_query($query,$link) or die("Insertion Failed:" . mysql_error());


and all I get back is:

Insertion Failed:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Racket,images/990335249.jpg,Ball)' at line 1.

Can anyone shed some light?

Thanks

Matthew
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Hey Matt,

It's probably best to go ahead and place all your data into variables so you can make sql safe queries and easier to debug. The value for the variables could have characters that are not allowed in the column type of your MySQL or the value is making the query fail. Though, I can see right off you query is wrong... you need single quotes around your values
Code: [ Select ]

$query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES (". $product . "," . $_SESSION['ID'] . ",". $description . ",". $target_path . ",". $itemwanted . ")";

// this essentially makes the following query if you change the variables to their data


$query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES (WidgetXYZ,3,item details made in USA,file/path/to.jpg,item_wanted_value)";


// you need single quotes

$query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES ('". $product . "','" . $_SESSION['ID'] . "','". $description . "','". $target_path . "','". $itemwanted . "')";
  1. $query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES (". $product . "," . $_SESSION['ID'] . ",". $description . ",". $target_path . ",". $itemwanted . ")";
  2. // this essentially makes the following query if you change the variables to their data
  3. $query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES (WidgetXYZ,3,item details made in USA,file/path/to.jpg,item_wanted_value)";
  4. // you need single quotes
  5. $query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES ('". $product . "','" . $_SESSION['ID'] . "','". $description . "','". $target_path . "','". $itemwanted . "')";


Also, check your target path variable as it may contain characters that aren't acceptable to the query or the database. The following may clear up some things.

Code: [ Select ]
function inject_protect($value)
{
$v = mysql_real_escape_string($value);
return $v;
}
$product = inject_protect($_POST['Product']);
$description = inject_protect($_POST['Description']);
$itemwanted = inject_protect($_POST['Itemwanted']);
$sid = inject_protect($_SESSION['id']);


$query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES ('$product','$sid','$description','$target_path','$itemwanted')";
  1. function inject_protect($value)
  2. {
  3. $v = mysql_real_escape_string($value);
  4. return $v;
  5. }
  6. $product = inject_protect($_POST['Product']);
  7. $description = inject_protect($_POST['Description']);
  8. $itemwanted = inject_protect($_POST['Itemwanted']);
  9. $sid = inject_protect($_SESSION['id']);
  10. $query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES ('$product','$sid','$description','$target_path','$itemwanted')";
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

devilwood wrote:
Code: [ Select ]
function inject_protect($value)
{
$v = mysql_real_escape_string($value);
return $v;
}
$product = inject_protect($_POST['Product']);
$description = inject_protect($_POST['Description']);
$itemwanted = inject_protect($_POST['Itemwanted']);
$sid = inject_protect($_SESSION['id']);


$query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES ('$product','$sid','$description','$target_path','$itemwanted')";
  1. function inject_protect($value)
  2. {
  3. $v = mysql_real_escape_string($value);
  4. return $v;
  5. }
  6. $product = inject_protect($_POST['Product']);
  7. $description = inject_protect($_POST['Description']);
  8. $itemwanted = inject_protect($_POST['Itemwanted']);
  9. $sid = inject_protect($_SESSION['id']);
  10. $query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES ('$product','$sid','$description','$target_path','$itemwanted')";

I don't get why you're doing it that way, you're ccreating a function, just to call another function, and you're declaring variables that have already been declared, you can do all of that in one line:
Code: [ Select ]
$query = "INSERT into Product (Product,UserID,Description,Picture,Itemwanted) VALUES ('" . mysql_real_escape_string($_POST["Product"]) . "','" . mysql_real_escape_string($_POST["id"]) . "','" . mysql_real_escape_string($_POST["description"]) . "','" . mysql_real_escape_string($target_path) . "','" . mysql_real_escape_string($_POST["Itemwanted"]) . "')";
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

You're right.

My point was for debugging purposes and actually you're variables are not declared. You'd actually get a NOTICE error saying those variables aren't declared but it's not a big deal cause that reporting is turned off on most production servers in their apache config or whatever server they're using. So, if you set the POST values to a variable you can echo them to see exactly what the data looks like or I guess you could just echo your $query variable to see if the values change the SQL.

The function is just to make escaping the values easier. Ultimately, you could do more cleaning inside the function inject_protect like trimming, etc and then actually put that function in an include file that you would include with all your php files.

Let me know if that helps and if you have any more questions.
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Okay cool, now I get why you're using the function, that makes a lot of sense ...

@confuzzled1234 - The only issue with your query is that some values tht go into a database need to have quotation marks around them because it is a string value. This counts for quite a few column types for example: VARCHAR, TEXT, CHAR, BLOB etc.

also remember that sometimes your column names could be MySql reserved words and that if a column is a reserved word, you don't necessarily have to chnge its name, you could just "escape" it by using the ` key (that is the key one left of the number one(1) so for instance

Code: [ Select ]
CREATE TABLE `table`(
`column` INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
`data` VARCHAR(255) NOT NULL DEFAULT 'Hello World'
);
  1. CREATE TABLE `table`(
  2. `column` INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
  3. `data` VARCHAR(255) NOT NULL DEFAULT 'Hello World'
  4. );


In that example you can see that the words "table", "column" and "data" are reserved words, but they are escaped so they won't cause any errors.

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 56 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.