CODEWITHSUNDEEP
php
user2958635
user2958635

Reputation: 1

How to solve parameters in MYSQLi?

I am having a problem in my php file named JO-dashboard.php. It displays the error presented below the code.

Here is my code:

<?php
$link = connectToDB();
$strXML = "<chart caption='Factory Output report' subCaption='By Quantity'   pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>";   

$strQuery = "select DISTINCT profile from vgprofile";

$result = mysqli_query($link, $strQuery) or die(mysqli_error());

if($result) {
    while ($ors = mysqli_fetch_array($result)) {
        $strQuery = "select sum(MT) as totalLM from tbljocreator where PROFILE =" .    $ors['profile'];
        $result2 = mysqli_query($link, $strQuery) or die(mysqli_error());
        $getresult2 = mysqli_fetch_array($result2); 
        $strXML .= "<set label='" . $ors['profile'] . "' value ='" . $getresult2['totalLM'] . "'  />";
        mysqli_free_result($result2);
    }
}
mysqli_close($link);
$strXML .= "</chart>";

echo renderChart("FusionCharts/Column3D.swf", "", $strXML, "JoCreator", 450, 300, false, true);

?>

THE ERROR IS IN:

$result2 = mysqli_query($link, $strQuery) or die(mysqli_error());

In the browser it shows:

Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\xampp\htdocs\LearningFusionCharts\MyFirstChart\JO-dashboard.php on line 29

Upvotes: 0

Views: 125

Answers (4)

Theraot
Theraot

Reputation: 40190

The initial problem

You have to pass the connection object to the function mysqli_error, like this:

$result = mysqli_query($link, $strQuery) or die(mysqli_error($link));

And this...

$result2 = mysqli_query($link, $strQuery) or die(mysqli_error($link));

Note: your code must have another problem that will be revealed after you do this. PHP wouldn't be executing the part of mysqli_error if there weren't an error in the query or something related to it.

The hidden problem

In fact, I have reasons to think* the problem is that $ors['profile'] is string, and therefore it should be between quotation marks in the query string:

$strQuery = 'select sum(MT) as totalLM from tbljocreator where PROFILE = "' . $ors['profile'] . '"';

*: This was confirmed in the comments. The error was:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIVETS' at line 1

In this case RIVETS is the value of $ors['profile'] and evidently it is an string, ergo it must go between quotation marks...but that doesn't mean it is safe.

SQL Injection

We could say that your code is correct, the same code will probably work is the data where different. Yet, since the values you are putting in the query string may not be entirely safe (even with the data comming from the database), you will have to escape the dangerous characters.

This is put in evidence by the error you got:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"CUTTING DISC 4""' at line 1

In this case, the variable $ors['profile'] got the string CUTTING DISC 4". This value is comming from the database and is causes a problem. It contains 4" meaning four inches, but Mysql sees the quotation marks (") and thinks that that is the end of the string, and tries to interpret whatever comes after the quotation marks as SQL.

If this input weren't from the database, but form the user... it would be worst... a malicious user could take adventage of it to execute arbitrary commands in the database. The potential of this kind of attack is overwhelming.

I recommend the video Hacking Websites with SQL Injection - Computerphile, it is a very good introduction to SQL injection for those beginners to web security, database security or information security in general. To learn more about what can be potentially be done with this kind of attack, read SQL Injection Walkthrough (DVWA) by Trenton Ivey.

Preventing SQL Injection - The old way

The old way to solve this problem is to escape the characters. SQL allows to do so by using the backslash character (\). So, in this example you would have to pass 4\" instead of 4". But that is the tip of the iceberg, there are plenty of security problems with it.

Something you could do for ease of migration is to declare a function to sanitize the data you send to the database, the idea is to escape any possibly treating character... in fact there is a function for that in PHP (mysql_real_escape_string):

$strQuery = 'select sum(MT) as totalLM from tbljocreator where PROFILE = "' . mysql_real_escape_string($ors['profile']) . '"';

The problems with the old way

But mysql_real_escape_string is deprecated and should not be used in new development (you would notice it is not form mysqli... ), this function has some quirks of itself too... for example there is no way to tell that function what character encoding you are using (it uses whatever the databases is using), and there has been reports of problems with it when using multibyte characters. That is the old way to solve this.

Here goes another recommendation: The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets by Joel Spolsky. I understand if you don't want to read... get another video: Characters, Symbols and the Unicode Miracle - Computerphile

Preventing SQL Injection - The new and improved way

With that said, the correct solution is to migrate to prepared statements, they are not really that hard with mysqli, it would be something like this:

$strQuery = 'select sum(MT) as totalLM from tbljocreator where PROFILE = ?';
if($stmt = $link->prepare($strQuery))
{
    //s for string
    //i for integer
    //d for double (or float)
    $stmt->bind_param('s', $ors['profile']);
    if (!$stmt->execute())
    {
    die mysqli_error($link);
    }
}
else
{
    die mysqli_error($link);
}

Read more about Prepared Statements at PHP.net.

Upvotes: 1

Lorenz Meyer
Lorenz Meyer

Reputation: 19915

Replace

mysqli_error()

by 

mysqli_error($link)

By the way, if the error message is as clear, you don't need to ask here. Just read the manual.

Upvotes: 0

Angus Walker
Angus Walker

Reputation: 398

Just change 

mysqli_error()

to

mysqli_error($link)

in each of the places where it occurs.

ie 4th line:

$result = mysqli_query($link, $strQuery) or die(mysqli_error($link));

and 8th line:

$result2 = mysqli_query($link, $strQuery) or die(mysqli_error($link));

Upvotes: 0

CaptainCarl
CaptainCarl

Reputation: 3489

the mysqli_error function requires a parameter. https://www.php.net/mysqli_error

p.s clean up your code and using tabs :)

Upvotes: 1

Related Questions