Asked  7 Months ago    Answers:  5   Viewed   26 times

I'm playing around with MySQLi at the moment, trying to figure out how it all works. In my current projects I always like to echo out a query string while coding, just to make sure that everything is correct, and to quickly debug my code. But... how can I do this with a prepared MySQLi statement?

Example:

$id = 1;
$baz = 'something';

if ($stmt = $mysqli->prepare("SELECT foo FROM bar WHERE id=? AND baz=?")) {
  $stmt->bind_param('is',$id,$baz);
  // how to preview this prepared query before acutally executing it?
  // $stmt->execute();
}

I've been going through this list (http://www.php.net/mysqli) but without any luck.


EDIT

Well, if it's not possible from within MySQLi, maybe I'll stick with something like this:

function preparedQuery($sql,$params) {
  for ($i=0; $i<count($params); $i++) {
    $sql = preg_replace('/?/',$params[$i],$sql,1);
  }
  return $sql;
}

$id = 1;
$baz = 'something';

$sql = "SELECT foo FROM bar WHERE id=? AND baz=?";

echo preparedQuery($sql,array($id,$baz));

// outputs: SELECT foo FROM bar WHERE id=1 AND baz=something

Far from perfect obviously, since it's still pretty redundant — something I wanted to prevent — and it also doesn't give me an idea as to what's being done with the data by MySQLi. But I guess this way I can quickly see if all the data is present and in the right place, and it'll save me some time compared to fitting in the variables manually into the query — that can be a pain with many vars.

 Answers

77

I don't think you can - at least not in the way that you were hoping for. You would either have to build the query string yourself and execute it (ie without using a statement), or seek out or create a wrapper that supports that functionality. The one I use is Zend_Db, and this is how I would do it:

$id = 5;
$baz = 'shazam';
$select = $db->select()->from('bar','foo')
                       ->where('id = ?', $id)
                       ->where('baz = ?', $baz); // Zend_Db_Select will properly quote stuff for you
print_r($select->__toString()); // prints SELECT `bar`.`foo` FROM `bar` WHERE (id = 5) AND (baz = 'shazam')
Wednesday, March 31, 2021
 
Fredy
answered 7 Months ago
19

I worked on the Zend_Db_Adapter_Mysqli and Zend_Db_Statement_Mysqli classes quite a bit to get this to work, since we wanted to make it conform to the PDO and PDOStatement interface. It was pretty laborious, because of the confusing way MySQLi insists on making you bind variables to get results, and the variety of fetch modes supported by PDOStatement.

If you want to see the code in Zend_Db, pay special attention to the functions Zend_Db_Statement_Mysqli::_execute() and fetch(). Basically, the _execute() method binds an array of variable references using call_user_func_array(). The tricky part is that you have to initialize the array so the bind_result() function gets the references. Uh, that wasn't totally clear, so go take a look at the code.

Or else just use PDO's MySQL driver. That's what I would do in your shoes.

Wednesday, March 31, 2021
 
Manju
answered 7 Months ago
76

White Listing

Your code in it's current form is very dangerous, not only do you allow the user to decide what fields should be selected but you also allow him to decide what tables to query on. You should definitely carry out white list checking on these. eg:

if($_POST['tableSelected'] == 'acceptable_table1' || $_POST['tableSelected'] == 'acceptable_table2) {
    $table = $_POST['tableSelected']
}

Similarly you should validate the field lists. But field list validation is going to be rather complicated because your fields are going to be dependent on the table. I suggest creating arrays and checking that the selection is in it.

$table1_fields = array('col1','col2',...)
$table2_fields = array('col1','col2',...)

Prepared Statements

As you know prepared statements can only be used to bind parameters. They cannot be used to fill in table names and column names. That's why you need both prepared statements and white listing. I recommend using PDO. It might look something like

$stmt = $dbh->prepare("SELECT {$fieldlist} FROM {$table} where field = ?");
$stmt->execute(array('somevalue'));
Saturday, May 29, 2021
 
Laimoncijus
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 4 Months ago
36

This is how your code should look (with added SQL Injection protection):

<?php
include "dbinfo.php"; //contains mysqli_connect information (the $mysqli variable)
//inputs
$name = mysqli_real_escape_string($_GET['name']);
$text = mysqli_real_escape_string($_GET['text']);

$sqlqr = "INSERT INTO `ncool`.`coolbits_table` (`name`, `text`, `date`) VALUES ('" . $name . "', '" . $text . "', CURRENT_TIMESTAMP);";

mysqli_query($mysqli,$sqlqr); //function where the magic happens.
?>

Take a look at what I've done. Firstly I've escaped the user input you're retrieving into the $name and $text variables (this is pretty much a must for security reasons) and as others have suggested you should preferably be using prepared statements.

The problem is that you weren't surrounding string values with single quotes ('), which is a requirement of the SQL syntax.

I hope this helps to answer your question.

Thursday, September 2, 2021
 
Pachvarsh
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :