Organizational Research By

Surprising Reserch Topic

Experts Most Trusted Topic


properly escaping with mysqli query over prepared statements using -'php,mysqli'


properly escaping with mysqli query over prepared statements  using -'php,mysqli'

I have read this:

will help you NOT against injection.
Beause escaping is just a string formatting facility, not injection preventer by any means.
Go figure.
However, escaping have something in common with prepared statements:
Them both doesn't guarantee you from injection if
you are using it only against notorious "user input", not as a strict rule for the building ANY query, despite of data source.
in case you need to insert not data but identifier or a keyword.

On the following Post: Are dynamic mysql queries with sql escaping just as secure as prepared statements?

So my question is that using:

$Var = "UserInput Data Possible SQL Injection";
$mysqli->real_escape_string($Var);


does not provide protection against SQL Injection?

I want to use $mysqli->query(); so I can use fetch_array(MYSQLI_ASSOC); Because to be frank, I have no idea how to fetch the results as an array after using a prepared statement.

So If I have this in my Database Connection:

$STD = new mysqli('localhost', 'root', 'xx', 'xx');
$STD->set_charset('utf8');

if ($STD->connect_error) {
    die("Standard Access Has Been Revoked. Please Contact Administration");
}elseif (!$STD){
die ("Other problem With Connecting To Database, Please Contact Administration");
}


as stated in the manual for real_escape_string

http://php.net/manual/en/mysqli.real-escape-string.php

The above lists:

*Caution
Security: the default character set
The character set must be set either at the server level, or with the API function mysqli_set_charset() for it to affect mysqli_real_escape_string(). See the concepts section on character sets for more information.*

Which links to: http://php.net/manual/en/mysqli.set-charset.php



My overall question can split into three options, the first would be asking for a fetch_array() equlivant for prepared statements, which will provide full SQL injection prevention due to prepared statements sending data as raw.



The first question in this format follows:

I'm using a Query as:

$GetCompletedQuery = $STD->query("SELECT Status FROM UserCompletion WHERE `UserID`=' ". $STD->real_escape_string($_SESSION['UID']) ."'");
$GetCompletedArray = $GetCompletedQuery->fetch_array(MYSQLI_ASSOC);


Which returns:


  Array ( [Status] => 1 )


But using prepared statements:

$GetCompletedQuery = $STD->prepare("SELECT Status FROM UserCompletion WHERE `UserID`=?");
$GetCompletedQuery->bind_param('i', $_SESSION['UID']);
$GetCompletedQuery->execute();

$GetCompletedArray = $GetCompletedQuery->fetch_row;

print_r($GetCompletedArray);


Which returns:


  Fatal error: Call to a member function fetch_row() on a non-object in /var/www/New/API/Constants.php on line 17


The same appears when I try fetch_array() which I know cannot be used with prepared statements.

So what would be the option for using prepared statements?



Second Question

If I use My Usual Query as:

$GetCompletedQuery = $STD->query("SELECT Status FROM UserCompletion WHERE `UserID`=' ". $STD->real_escape_string($_SESSION['UID']) ."'");


which enabled me to use fetch_array();  is data properly secured from SQL injection?



Third Question:

Should I be escaping/protecting from SQL injection for a $_SESSION['UID']; as this is assigned in the following manor:

$InnerJoinQuery = $STD->query("
        SELECT Users.ID, Users.Username, Users.Password, UserInformation.LastName, UserInformation.Firstname, UserInformation.DOB
        FROM Users
        INNER JOIN UserInformation
        ON Users.ID = UserInformation.UserID WHERE Users.Username = '".$_SESSION['real_name']."'");
        $InnerJoinArray = $InnerJoinQuery->fetch_array(MYSQLI_ASSOC);

    $_SESSION['UID'] = $InnerJoinArray['ID'];
    $_SESSION['Password'] = $InnerJoinArray['Password'];
    $_SESSION['Firstname'] = $InnerJoinArray['Firstname'];
    $_SESSION['LastName'] = $InnerJoinArray['LastName'];
    $_SESSION['DOB'] = $InnerJoinArray['DOB'];


This snippet explained:

User Logs in with username & password, the file gets information from the database based on $_SESSION['real_name'];
and adds to the $_SESSION array with the results, adding each into a different key.

The question for this chunk is should I even be escaping/protecting from SQL injection when the $_SESSION['UID']; is assigned through the database based on $_SESSION['real_name'];

Thankyou for your time for reading over this massive chunk.
    

asked Sep 7, 2015 by rajesh
0 votes
5 views



Related Hot Questions



Government Jobs Opening


...