Did I do the MySQL escape correctly?
require_once('scripts/connect.php');
$domainSearch = $_POST['search'];
$escapedSearch = mysqlirealescape_string($dbconnect,$domainSearch);
$dbsearch = 'SELECT * FROM domains WHERE domain_name LIKE "'.$escapedSearch.'.ssu.lt"';
$dbquery = mysqli_query($dbconnect,$dbsearch) or die ('Unable to execute query');
while($row = mysqlifetchassoc($dbquery)) {
echo "
````
It goes into a lot of HTML after that.
4 Replies
mysqli_prepare does this for mysqli
I'd also advise PDO or another similar database abstraction method because it gets to be really convoluting to do multiple queries that way.
The gist of PDO is that you use something called a "database handle" to prepare your queries based upon binded arguments/parameters.
So your query would look something like "SELECT * FROM foo WHERE field1 = ? AND field2 = ?". Then, you generate a statement based upon the prepared query and execute it and use PDO's built-in accessor methods to view the results of your query. PDO handles all of the escaping for you. It's fairly industry-standard.
The only downside about PDO prepared statements is that, because of how it processes queries (as prepared statements), you won't be able to ever see the populated query, but you will have access to error output based on PDO's internal error messages which are very useful for debugging.
Here is an example of a simple database class you can use:
class Database extends PDO {
private $engine;
private $host;
private $database;
private $user;
private $pass;
function __construct(){
$this->engine = 'mysql';
$this->host = '#hostname#';
$this->database = '#db_name#';
$this->user = '#db_user#';
$this->pass = '#db_pass#';
$dns = $this->engine.':dbname='.$this->database.";host=".$this->host;
try {
parent::__construct( $dns, $this->user, $this->pass);
} catch(PDOException $e) {
// Log error
#echo $e->getMessage();
}
}
function __destruct() {
$this->dbh = null;
}
}
along with a simple class example
class Size {
private $id;
private $dbh;
var $size_group_id;
var $description;
function __construct(Database $dbh) {
$this->dbh = $dbh;
}
function getSizes() {
$dbh = $this->getDatabaseHandle();
$query = "SELECT * FROM sizes";
$statement = $dbh->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_CLASS);
if (!empty($result))
return $result;
}
private function getDatabaseHandle() {
return $this->dbh;
}
}
So you would just create a new object of the object class (Size, in the example) that includes (or extends) the Database class and then bake your logic into methods within the applicable object class.
So inside of your content-displaying page, you'd have something like:
$size_access = new Size();
$sizes = $size_access->getSizes();
and it would call on the getSizes() method in Size.class.php which would in this case include PHP's PDO class as an instantiation in the Size class constructor (__construct() function) and give you a database handle you could use throughout that class.
One major advantage is that PDO also has its own handler allocation functions so that it efficiently closes and opens new connections so you don't have to worry about multiple calls to the database staying alive and shutting down. Generally folks just shut down PDO in a __destruct() method in a class or just simply let PDO handle garbage collection and closing connections.
I hope this assists you in your exploration.
So it'd be like:
$query = "SELECT * FROM foo WHERE field1 = ? AND field2 = ?";
and then
$statement->execute( array($field_1, $field_2) ); // Takes an array of values that binds to the exact number of ? parameters in $query
or, as I prefer to do it for cleanness:
$values = array(
"field_1" => $this->getField1(), // field-to-class-variable database mapping
"field_2" => $this->getField2()
);
$statement->execute( array_values($values) );
It's very powerful and worth looking into for sure. PHP docs on it have pretty straightforward documentation too.