$SQL UPDATE doesn't work when added WHERE CLAUSE

Question

I know I am simply missing the simplest thing here but cant seem to figure it out.

so this works with this code but changes all rows of the database as opposed to just the one with the page id...

 <?   $pageid= $_GET["id"];
 $sql = "SELECT id, first_name, last_name, email, bio, job, job2, job3 FROM `".weapons."`            WHERE id = $pageid"; 


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

 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $job = $_POST['job'];
 $job2 = $_POST['job2'];
 $job3 = $_POST['job3'];
 $bio = $_POST['bio'];
 $email = $_POST['email'];
 $sql = "UPDATE weapons SET first_name='$first_name', email='$email' , job='$job',      job2='$job2', job3='$job3', bio='$bio', last_name='$last_name'";
 if (@mysql_query($sql)) {
 echo('<p>Update Complete</p>');
  } else {
  echo('<p>Error updating: ' . mysql_error() . '</p>');
  }

 }else{   ...

however when adding the WHERE clause, like as follows

 $sql = "UPDATE weapons SET first_name='$first_name', email='$email' , job='$job',      job2='$job2', job3='$job3', bio='$bio', last_name='$last_name' WHERE id = $pageid";

I get an error

Error updating: 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 '' at line 1

Any help would be great, thanks

EDIT

I actually missed a super easy thing, which is what I initially assumed, I had at first in my form had <form method='post' enctype='multipart/form-data' action='submit.php'> however that obviously messed up the get id because there isnt an idea, so even if that page was submit.php?id=4 when you hit submit it wouldnt run because the id would be gone.

Switching the code to <form method='post' enctype='multipart/form-data' action='#'> did just the trick.

Thanks for the help guys and I am looking into the sql injection now and working on how to better secure my site.

Any help would be great, I know the issue lies with getting the pageID passed into the sql as the id — Chris James Champeau, Nov 01 '11 at 23:08

score 3 · Answer 1 · edited May 23 '17 at 12:01

3

Please escape your strings before you create your SQL statement. Various characters in your input values will both break your query and open a HUGE security hole. That may very well be your problem. Look at this post for more info How can I prevent SQL injection in PHP?

enter image description here

In short, you assignments would look like this:

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

echo $sql; before you run it and post what that outputs.

edited May 23 '17 at 12:01

Community

1
1

answered Nov 01 '11 at 22:22

Code Magician

23,217
7
60
77

Beat me to it, but yes, try escape the input. – MrE Nov 01 '11 at 22:23
UPDATE weapons SET first_name='Shane', email='shane@weaponsofme.com' , job='Director', job2='Editor', job3='', bio='bio will go here someday soon', last_name='McLafferty' WHERE id = – Chris James Champeau Nov 01 '11 at 22:26
It seems to be an issue with it getting the id – Chris James Champeau Nov 01 '11 at 22:27
1

you can use if(isset($_GET['id'])&&$_GET['id']!='') to test to see if an id was passed in. Also, ideally you should use regular expressions or filter_input() to remove non-numeric chars from your id param. – Code Magician Nov 01 '11 at 22:30
something like this?? `$padeid= filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_FLOAT);` ...because that didnt work – Chris James Champeau Nov 01 '11 at 22:40
filter_input will only filter the input for the expected chars for security, it won't give it a value if there is no value. – Code Magician Nov 02 '11 at 05:11

$SQL UPDATE doesn't work when added WHERE CLAUSE

1 Answers1