PHP update MySQL table with external values

Question

I have a mysql table that is connected to xcode via php, Im trying to update a cell using this code, and is returning nothing in the table.

<?php

$conn = mysql_connect($host, $user, $pass);
@mysql_select_db($db) or die("Unable to find database");

$routeID = $_GET["routeID"];
$newComment = $_GET["newComment"];

$query = "UPDATE routes SET comment = '$newComment' WHERE routeID='$routeID'";

mysql_query($query) or die (mysql_error("error"));
mysql_close();
?>

If I changed $routeID to routeID='routeID' or routeID=routeID it would update the entire comment column and add the actual id into it e.g. test?routeID=1

If I changed $routeID to routeID=1 or 20 etc. it would update the correct row. Any ideas on whats wrong with this.

Could you post the code that actually runs the query? Also, if `routeID` is a integer column you should not be quoting the value in the query. — Sammitch, Mar 05 '14 at 18:06
The two paragraphs beneath your code are utterly indecipherable. — eggyal, Mar 05 '14 at 18:12
@eggyal im retrieving `$routeID = $_GET["routeID"];` and `$newComment = $_GET["newComment"];` values from xcode — Ernie, Mar 05 '14 at 21:15

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

It appears that your querystring is currently newComment=test?routeID=1, whereas it should be newComment=test&routeID=1.^*

Consequently, PHP parses the current querystring as a single name newComment with the value test?routeID=1 rather than two names newComment and routeID with values test and 1 respectively.

However, please note that you absolutely must not simply concatenate values from the querystring directly into your SQL: so doing can lead to bugs if the values are not what was expected, which can be exploited by attackers to compromise your database. See How can I prevent SQL injection in PHP?

Please also note that, as documented under mysql_connect():

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

mysqli_connect()

PDO::__construct()

Finally, please note that the (optional) argument to mysql_error() is the MySQL link identifier resource, $conn in your case: passing a string literal such as "error" will result in its failure.

^{* As documented under Data Handling, the default value for arg_separator.input (which is described as "List of separator(s) used by PHP to parse input URLs into variables.") is "&". This is consistent with the encoding used by browsers to submit form data, signified by the application/x-www-form-urlencoded MIME type.}

thanks for explaining, xcode was passing with `?` changed to a `&` and is working — Ernie, Mar 10 '14 at 15:15

PHP update MySQL table with external values

1 Answers1

Warning