1

I have the below code:

$result = $conn->query($sql)->fetch(PDO::FETCH_COLUMN);
$myArray = explode(',', $result);
$sql = ("SELECT username,status,location FROM Users WHERE username IN ('"    . implode("','",$myArray) . "') AND status > 0 ORDER BY username");
$data = $conn->query($sql)->fetchAll(PDO::FETCH_OBJ);
print_r ($data);

FYI $result is "kk,bb"

The output is as follows:

Array ( [0] => stdClass Object ( [username] => bb [status] => 1   [location] => ) [1] => stdClass Object ( [username] => kk [status] => 1   [location] => ) )

But I really just want to see a two-dimensional array without it saying stdClass Object in front of each item. I tried different kinds of fetching, but this is the only one I could make work. I know there are functions for converting object to array, but I´d rather if there´s a way to do this right, if there is such a thing, in the pasted code.

I hope someone can help, thanks in advance!

Improve this question
3
  • But I really just want to see a two-dimensional array without it saying stdClass Object in front of each item. Then why on earth are you requestion objects using PDO::FETCH_OBJ? Just remove or change that constant to FETCH_ASSOC and your problem is solved. Commented Jan 25, 2017 at 0:31
  • Maybe you can use PDO::FETCH_ASSOC? Commented Jan 25, 2017 at 0:32
  • All of you are right, and I have no clue why I didn´t manage to come up with that - I was more than convinced that I´d tried that already with an empty output - thanks!!! Commented Jan 25, 2017 at 0:38

3 Answers 3

Reset to default
3

You requested objects by using PDO::FETCH_OBJECT. If you want to fetch as arrays, use another fetch type:

$data = $conn->query($sql)->fetchAll(PDO::FETCH_ASSOC);

or

$data = $conn->query($sql)->fetchAll(PDO::FETCH_NUM);

The other choices are documented here: http://php.net/manual/en/pdostatement.fetch.php

By the way, why do you run one query to get the values in $myArray, then use these in an SQL-injection vulnerable way in a second query? Wouldn't it be easier to use a JOIN?

Re your comment:

Fist of all, I strongly urge you to code safely the first time, don't rely on "going back later." You probably won't have time to go back later, because once the code seems to work "good enough," the temptation is to go live immediately. Even if you do go back later to fix security flaws, you might miss one. Either way, your development process creates a high risk that you will go live with insecure code.

A piece of old advice from famous computer scientist Andrew S. Tanenbaum: "Security, like correctness, is not an add-on feature."

Now about joins. If you use SQL, you should understand how to do a joins. Not knowing joins in SQL is like thinking you know PHP even though you don't know how to use foreach(). Technically, you can still do some stuff, but you're missing out on a major part of the language.

Joins allow you to query multiple tables in one query, and find rows from each table that match.

In your example, I don't know your first query, but I'll take a guess that it queries some other table to get a list of usernames. Maybe it queries a list of users who are recipients of a email, like this:

SELECT username FROM EmailRecipients WHERE EmailId = 1234

Then your code uses the result list of usernames in a second query. But you can do it all in one query:

SELECT Users.username, Users.status, Users.location
FROM Users JOIN EmailRecipients 
  ON Users.username = EmailRecipients.username
WHERE EmailRecipients.EmailId = 1234

The SQL engine searches the EmailRecipients table and say it finds six rows for the recipients of email 1234. For each of the six rows, it looks up the corresponding row in the Users table with the matching username. Then it uses columns from those rows in the SELECT result.

When you join tables, there's a possibility both tables might have some columns with the same name. That's why I showed column named by qualifying them as belonging to one table or the other. This removes the ambiguity about which table you meant in each case.

It doesn't matter which table you list first. This type of join is the same left-to-right as it is right-to-level, like some expressions in algebra (2 + 4 is the same as 4 + 2, etc.). MySQL automatically figures out which table is most efficient to read first.

There's more to learn about joins, but that should get you started. I suggest you pick up a tutorial or a book on SQL sometime.

Re your comment:

Where is the "SQL-injection vulnerable way in a second query"?

In your code:

$result = $conn->query($sql)->fetch(PDO::FETCH_COLUMN); $myArray = explode(',', $result); $sql = ("SELECT username,status,location FROM Users WHERE username IN ('" . implode("','",$myArray) . "') AND status > 0 ORDER BY username"); $data = $conn->query($sql)->fetchAll(PDO::FETCH_OBJ);

There's no guarantee that the elements in $myArray are safe to use in an SQL statement. Just because they came out of the database doesn't make them safe. What if they contain names like "O'Reilly"? That will upset your quote-implosion.

You can never be 100% sure the data is safe, so it's better to use query parameters. Then you don't care if it's safe, because bound parameters are never combined with the SQL query string.

Here's how to create a dynamic list for the IN ( ) predicate from an array, and bind parameter values to the query using PDO:

$result = $conn->query($sql)->fetch(PDO::FETCH_COLUMN);
$myArray = explode(',', $result);
$placeholders = implode(',', array_fill(0, count($myArray), '?'));
$sql = "
  SELECT username,status,location FROM Users 
  WHERE username IN ($placeholders) AND status > 0 
  ORDER BY username");
$stmt = $conn->prepare($sql);
$stmt->execute($myArray);
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Well, first of all - all the non injection stuff I will look into later before my project goes live. Would be great if there´s a website outthere which can automatically transform all statements in a php btw to safe ones :) I´m not familiar with JOIN, how would this work?
Thanks alot Bill Karwin! You have a valid point on what you say about getting it right the first time. Luckily I have no actual deadline - just a long list of todo´s, where this is a later task to look into. The whole security thing cannot be compromised for what I´m doing here, and I have already made myself aware of that nobody will want to use what I´m developing if it´s not safe. Thanks for the JOIN lesson, I will try to see if the code can be rewritten more effeciently!
Not sure if it´s the right thing here to comment on something that´s more than 2 months old, but now I´m sitting and going through all my PHP code, which isn´t THAT much and want to secure it against injections. As I´ve understood it, things are safe in queries when using a PDO object, which is the case with the two queries in the code. So, I´d be happy to know where I´m wrong: Where is the "SQL-injection vulnerable way in a second query"?
Thanks for your great help Bill, will try to figure out how to do it right!
2

While both answers are perfectly valid, I just wanted to add in that there is another solution to your issue.

You can change the default fetch style by defining it where you connect to the database:

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$mydb = 'mydbname';

$conn = new PDO("mysql:host=$host;dbname=$mydb", $user, $pass, [
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

Or you can change it by using setAttribute() using the existing connection:

$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Then you can simply use:

$conn->query($sql)->fetchAll();

And you still have the ability to overwrite the default value:

$conn->query($sql)->fetchAll(PDO::FETCH_NUM);
Improve this answer

1 Comment

Very useful indeed!
1

using PDO::FETCH_ASSOC you can make it as an array. So

$data = $conn->query($sql)->fetchAll(PDO::FETCH_ASSOC);

Documentation link:

PDO Statement

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.