PHP PDO returning null column names

Question

I am developing SOAP service in PHP.
Recently I have came across an issue when retrieving results from MySQL.
Problem
When retrieving results I get more columns than specified in my SQL statement. This is affecting my code when processing retrieved information. For example when I change fetch() to fetchAll(), I can't process more than one record. Is there any way to tell PHP or MySQL to return EXACTLY the columns that I need and not return this rubbish?
example code

$stmt2 = $pdo2->prepare("SELECT `id`, `username`, `IP_address` FROM PADR_users WHERE `username` LIKE :username");
$stmt2->bindParam(':username',$searchParam, PDO::PARAM_STR, 20);
$stmt2->execute();
$tmp = $stmt2->fetch(PDO::FETCH_ASSOC);
$response->return = var_export($tmp, true);

This will produce:

object(stdClass)#4 (1) { ["return"]=> string(193) "array ( 'id' => '19', 'username' => 'Batman2', 'IP_address' => '192.168.0.1' . "\0" . '' . "\0" . '' . "\0" . '' . "\0" . '' . "\0" . '' . "\0" . '' . "\0" . '' . "\0" . '' . "\0" . '', )" }

Before I call MySQL I also tried using these:

$pdo2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo2->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);

However it doesn't make even a slightest difference.
Note
I have left intentionally the fetch() and not fetchAll() to eliminate possibility that the implementation of fetchAll() could have influenced the results.

That looks like some bad information in your database for the IP address. Verify it on the command line or in PHPMyAdmin, and try trimming it before inserting it. And just assign your result instead of using var_export. — aynber, Apr 06 '17 at 16:11
@aynber `IP_address` is a `varchar` and is no different than the `username`. — XAMlMAX, Apr 06 '17 at 16:19
True, these null bytes I am NOT selecting, as you can see the query specifically asks for 3 columns. Is there a reason for the driver to IGNORE sql syntax or is that an undocumented feature of `PHP` lol — XAMlMAX, Apr 06 '17 at 16:26
The null bytes are a part of your IP address field, not another field. You can tell by the concatenation symbol (period) `'IP_address' => '192.168.0.1' . "\0"` — aynber, Apr 06 '17 at 16:27
I see!!! This would mean that the data contained in the database has that in there. Well spotted !! I will investigate it now. Thanks — XAMlMAX, Apr 06 '17 at 16:29
@aynber bravissimo ! that did it. I inserted nulls into the columns and it is now returning multiple results! If you can put that as an answer I'll be sure to accept it. — XAMlMAX, Apr 06 '17 at 16:33

score 1 · Accepted Answer · answered Apr 06 '17 at 16:36

You are only retrieving only 3 columns. However, the IP address has some null data at the end of it. You can tell by the concatenation operator between the strings:

'IP_address' => '192.168.0.1' . "\0" . '' . "\0" . '' . "\0" . '' 
                              ^      ^    ^      ^    ^      ^

PHP PDO returning null column names

1 Answers1