Asked  7 Months ago    Answers:  5   Viewed   30 times

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.

 Answers

29
  1. http://php.net/manual/en/mysqli-stmt.get-result.php
  2. Yes, but it is very bad practice:
    • it will help you in this case but only in this case and deceive with anything else
    • manual escaping is just silly, better let driver to do it for you
  3. YES, because there is no such thing like SQL injection but improper formatting ONLY

is that using $mysqli->real_escape_string($Var); does not provide protection against SQL Injection?

I didn't change my mind: sure, it doesn't.
It will do only if you enclose the resulting value in quotes (and set proper encoding using mysqli_set_charset() to be strict).

Look, SQL injection not something essential, existing on it's own, but it's rather mere a consequence. A consequence of improperly formatted query.
When creating a query, you have to properly format every part of it. Not because of whatever "injection" but for the sake of it. When you're going to insert a string into query, you HAVE to put it into quotes, or you will get a syntax error. When you're going to insert a string into query, you HAVE to escape these quotes were used to delimit this string, or you will get a syntax error. And so on. It is proper formatting that should be your concern, not scaring tales about injection. And as long as you have every dynamic query part properly formatted according to it's type - no injection ever could be possible

So, the source of variable or it's value should never be your concern. But only it's place in the query:

  • strings have to be enclosed in quotes and have these quotes escaped.
  • numbers have to be cast to it's type.
  • identifiers have to be enclosed in backticks and have these backticks doubled

When it's going for the static part of the query, hardcoded in the script, we don't use such strict standards - say, we're not enclosing every identifier in backticks.
But when it's going for the dynamical part of the query, applying formatting rules should be strict rule, as we cannot know variable content for sure.

By the way, there is another way to format your strings and numbers - prepared statements. It is not as convenient as it should be, but because it is using placeholders to represent your data in the query, it it recommended to use over silly manual formatting.

Wednesday, March 31, 2021
 
NewPHP
answered 7 Months ago
11

If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?'

But if you avoid things like this it is unlikely you will have problems.

Wednesday, March 31, 2021
 
Keat
answered 7 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
17

Having such a function is a good idea per se. It indicates that you are a programmer in your heart, not just a tinkerer that writes PHP from ready made blocks like a Lego figure. Such a function can greatly improve your code.

But with great power comes great responsibility. Such a function is a constant danger of SQL injection, through table and field names. You should take care of that. Not to mention it should be properly implemented using prepared statements for the data.

First of all, you will need a general purpose function to execute an arbitrary MySQL query using a query and an array of parameters. I have a simple mysqli helper function for you. It will be a basic function to execute all prepared queries:

function prepared_query($mysqli, $sql, $params, $types = "")
{
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

Now we can start constructing the SQL query dynamically. For this we will need a function that would escape identifiers

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

It will make identifiers safe, at least as long as you are using Unocode.

Now we can proceed to creation of the correct SQL string. We will need to create an SQL with placeholders, like this:

INSERT INTO `staff` (`name`,`occupation`) VALUES (?,?)

So let's write a function that would create a query like this

function create_insert_query($table, $keys)
{
    $keys = array_map('escape_mysql_identifier', $keys);
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    return "INSERT INTO $table ($fields) VALUES ($placeholders)";
}

And finally we can write the long-sought crud function:

function crud_insert($conn, $table, $data) {
    $sql = create_insert_query($table, array_keys($data));
    prepared_query($conn, $sql, array_values($data));
}

called like this

$args = ['name' => "D'Artagnan", "occupation" => 'musketeer'];
crud_insert($link, "test_table", $args); 
Saturday, August 21, 2021
 
anky
answered 2 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 :