DIfferent MySQL behaviour from MySQL query from PHP and directly at PHPMyAdmin

Question

I am using a PHP file to return a JSON array to an iOS app. The table that is being consulted has only 32 records. If I execute the iOS app, it receives an empty array when calling the PHP file. If I execute the PHP on the web browser, the result is also an empty array. If I run the query that is included in the PHP file directly in PHPMyAdmin, the query shows the correct result.

This is the PHP file:

<?php
$host= 'localhost';
$db = 'app_mujer';
$uid = 'XXXXXXXXXXXX';//
$pwd = 'XXXXXXXXXXXX';
$link = mysql_connect($host,$uid,$pwd) or die("No se puede conectar ");
mysql_query("SET NAMES 'utf8'");
mysql_select_db($db) or die ("No se puede seleccionar la bbdd");
$id= urldecode($_GET['id']);
$arr = array();
$rs = mysql_query("SELECT * FROM tbcoordenadas where titulo='$id'");
while ($obj = mysql_fetch_assoc($rs)){  
$arr[] = $obj['procedencia'];
}
echo json_encode($arr);
?>

I have detected that the problem occurs only when the URL parameter get by $id contains a '& character in it.

If I run the following query directly at PHPMyAdmin, the result is the expected record:

SELECT * 
FROM  `tbcoordenadas` 
WHERE  `titulo` =  'D & R'

Any help is welcome.

Well, you should not transfer that kind of data via GET. Not because it's not possible, but because of problems like yours. Why don't you transmit it via POST, then you will not need any decoding/encoding functions, which can break the string — Ivan Yonkov
– Ivan Yonkov, Commented Mar 22, 2014 at 15:30
Your code is vulnerable to SQL injections. You should read on how to prevent them in PHP. — Gumbo
– Gumbo, Commented Mar 22, 2014 at 15:30
@RoyalBg - The purpose of HTTP methods surpass the convenience that may be possible. Properly handling URL content with appropriate GET requests is preferred if it fits the purpose of the request. — Jared Farrish
– Jared Farrish, Commented Mar 22, 2014 at 15:33
Thank you @RoyalBg, I am not a PHP expert, and the code is taken from a tutorial and normally it is working for me. But in this case I have found the problem about the '&' character. You mean changing POST for GET will solved the issue? So easy? — mvasco
– mvasco, Commented Mar 22, 2014 at 15:33

Emilio · Accepted Answer · 2014-03-22 15:51:05Z

3

I have detected that the problem occurs only when the URL parameter get by $id contains a '& character in it.

That's where the problem is.

http://www.example.com/example.php?id=1&2&3

For this request, the value of $_GET['id'] will be 1 (and you would expect it to be 1&2&3). This is because the & symbol is used to add another URL parameter.

Since the value includes &, you need to urlencode() the ID before sending it to the PHP script. As pointed out by @Jared Farrish, this should be done on every GET value (to prevent problems such as this one).

edited Mar 22, 2014 at 15:51

answered Mar 22, 2014 at 15:35

Emilio

1861 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Jared Farrish Over a year ago

This is the reason, according to the OP's reason.

Xavier Rubio Jansana Over a year ago

POST has also the same problem, unless you use multipart encoding. But for regular application/x-www-form-urlencoded, the problem is exaclty the same. Solution is URL encoding your parameters, or encapsulate it in a different encoding (JSON, XML...). In this case this may be overkill, so just URL encode.

Ivan Yonkov Over a year ago

@XavierRubioJansana the POST will not go through the url, which will not inseparate the params

Jared Farrish Over a year ago

@RoyalBg - It's possible to submit POST data as url-encoded, which if handled exactly the same way, will end with the same result. POST is not a solution here, properly URL encoding the GET parameters is.

Jared Farrish Over a year ago

@mvasco - You need to urlencode() (or whatever is available on the platform) the URL parameters at the source of the request (browser, mobile app, etc.) so that the URL id argument looks like this: codepad.viper-7.com/DZYoME Note the encoded & between the D%20 and %20R.

|

Collectives™ on Stack Overflow

DIfferent MySQL behaviour from MySQL query from PHP and directly at PHPMyAdmin

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related