Updating a mysql db with php ajax request

Question

I have this php code that updates a row in my MySQL database, based on 3 variables sent with ajax but that returns a http 500 error:

<?php
$dbname = 'xxxxxxxxxx';
$dbuser = 'xxxxxxxxxxxx';
$dbpass = 'xxxxxxxxxxxxx';
$dbhost = 'xxxxxxxxx';
$link = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
$db_selected = mysql_select_db($dbname, $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$topparent = $_POST['name']; 
$column = $_POST['column']; 
$value = $_POST['value']; 

$sql = "UPDATE reloaded SET" . $column . " = '" .$value . "'WHERE top_parent = '" . $name ."';

$retval = mysql_query( $sql, $link );
         if(! $retval ) {
            die('Could not create table: ' . mysql_error());
         }
         echo "success\n";
         mysql_close($link);
?>

My jquery js is this. The variables get passed correctly (tried with alert):

function updatedb(a,b,c){
    $.ajax({
       url: "updatedb.php",
       type: 'POST',
       data: ({name:a,column:b,value:c}),
       success: function(msg) {
          alert('DB updated');
       }               
    });
};

Any idea why it returns an error? I have spent some time going over the code, trying different variations but can't seem to figure it out.

(1) you have missing quote in `$sql`. (2) you have syntax issues in `$sql`, as you do not have spaces after `SET`. (3) you are wide open to sql injection. you should whitelist your columns and sanitize your data https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Sean, Dec 17 '17 at 04:00
which version of PHP are you using?, mysql_* has been deprecated as of PHP 7 — Marcelo Origoni, Dec 17 '17 at 04:02
@Sean That post about injections is a bit complex for me, still a lot to learn. Would just adding an if statement checking against a fixed set of values be enough to protect the db? — cmplieger, Dec 17 '17 at 04:23

score 2 · Accepted Answer · edited Jan 05 '18 at 22:15

2

There is a PHP syntax error in the SQL query statement.

You have missed to end the " and hence the 500 error.

The corrected code:

$sql = "UPDATE reloaded SET " . $column . " = '" .$value . "' WHERE top_parent = '" . $name ."'";

Edit

Adding to that, there is no space after the SET keyword.

Fixing this will update your db properly.

edited Jan 05 '18 at 22:15

marc_s

732,580
175
1,330
1,459

answered Dec 17 '17 at 04:00

RamC

1,287
1
11
15

there is another syntax issue, in addition to being wide open to sql injection – Sean Dec 17 '17 at 04:03
ah... yes I see now, it now successfully executes, even though it doesn't seem to update the db yet, but that's another issue. – cmplieger Dec 17 '17 at 04:03
@Sean Yes you are right, added the space after SET. which anyways will not throw the 500 error. – RamC Dec 17 '17 at 04:04
@RamC no, it would not cause the 500 error, but would impact the db update that the OP said wasn't happening in [this comment](https://stackoverflow.com/questions/47852068/updating-a-mysql-db-with-php-ajax-request/47852092?noredirect=1#comment82668540_47852092) – Sean Dec 17 '17 at 04:06
yes sean, @cmplieger i've edited the answer, the db update should happen now. – RamC Dec 17 '17 at 04:18
I got it up and running. Also converted from MySQL to mysqli thanks to comments on initial post. Have to work on injection, although it's an internal app and not super important. Thanks for your help! – cmplieger Dec 17 '17 at 04:20

Updating a mysql db with php ajax request

1 Answers1