format php query result

Question

I have this code:

$list_summoners = $con->query("SELECT * FROM verified_users WHERE Username='" . $search_user . "'");

and I was wondering how I could format it, like:

$list_summoners = mysqli_query($con, "SELECT * FROM verified_users WHERE Username='" . $search_user . "'");
    echo '<table align="center" style="text-align:center;"><tr><th>User</th><th>Summoner ID</th><th>Summoner Region</th><th>View Summoner</th></tr>';
    while($row = mysqli_fetch_array($list_summoners)) {
        echo '<tr><td>' . $row['username'] . '</td><td>' . $row['summoner_id'] . '</td><td>' . $row['summoner_region'] .    '</td><td><a href="action.php?do=view_summoner&zone=c&summoner_id=' . $row['summoner_id'] . '&summoner_region=' . $row['summoner_region'] . '"><span class="button color_dark">View</span></a></td></tr>';  
    }
    echo '</table>';

I am asking this, because I know mysqli_query is open to abuse.

Thanks in advance.

It is unclear what you are asking. Do you need to know [how to prevent SQL injection with `mysqli_query()`](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)? — Michael Berkowski, Feb 02 '14 at 14:46
@MichaelBerkowski I'm asking how to do a while event for the array `$list_summoners` using `$con->query` rather than `mysqli_query` — user3256987, Feb 02 '14 at 14:48
Oh. `$con->query()` and `mysqli_query($con, 'SELECT...')` are _equivalent_. They are just two methods of performing exactly the same action, the first using an object-oriented style mysqli, the second using procedural style. There is _no difference_ between them in terms of functionality or security. They're both equally insecure in the context you've used them - solvable using [`mysqli::prepare()`](http://php.net/manual/en/mysqli.prepare.php) instead, as described in the linked question. — Michael Berkowski, Feb 02 '14 at 14:51
To loop with the first method, you would use `while ($row = $list_summoners->fetch_array())` as `$list_summoners` is a mysqli result object. — Michael Berkowski, Feb 02 '14 at 14:52

score 0 · Accepted Answer · edited May 23 '17 at 12:03

mysqli_query($con, 'SELECT...') called in procedural mode, vs $con->query('SELECT...') called in object-oriented mode perform exactly the same function. In both modes, $con is the same object - a mysqli connection object, but the MySQLi API offers two methods of interacting with it.

So, the use of mysqli_query() and $con->query() are both equally insecure when used the way you are using them, concatenating in a variable $search_user. The secure method would be to avoid mysqli_query() entirely and instead use a prepared statement:

$stmt = $con->prepare('SELECT * FROM verified_users WHERE Username = ?');
if ($stmt) {
  $stmt->bind_param('s', $search_user);
  $stmt->execute();
  // Then bind & fetch()...
}
else echo $con->error;

See How can I prevent SQL injection in PHP for more details & examples on executing and fetching from the prepared statement.

Using $con->query() as you are, to fetch rows with a while loop you may call $list_summoners->fetch_array() as it is an object of class mysqli_result

if ($list_summoners) {
  while ($row = $list_summoners->fetch_array()) {
    echo '<table align="center" style="text-align:center;"><tr><th>User</th><th>Summoner ID</th><th>Summoner Region</th><th>View Summoner</th></tr>';
    echo '<tr><td>' . htmlspecialchars($row['username']) . '</td><td>' . htmlspecialchars($row['summoner_id']) . '</td><td>' .  htmlspecialchars($row['summoner_region']) .    '</td><td><a href="action.php?do=view_summoner&zone=c&summoner_id=' . htmlspecialchars($row['summoner_id']) . '&summoner_region=' . htmlspecialchars($row['summoner_region']) . '"><span class="button color_dark">View</span></a></td></tr>';  
    echo '</table>';
  }
}

Note the addition of htmlspecialchars() to those values, when sent to output as HTML. Even if these were not originated from user input, it is an important habit to be in as it will prevent cross-site scripting when outputting values originating from user input, or values which contain characters requiring entity encoding in HTML.

format php query result

1 Answers1