BOOLEAN WHO ? (FROM WHERE Headbanger)

jazzman1 · February 4, 2014

Also, in order to escape characters the proper order shoud be:

 // Sanitize message input
   $data = stripslashes($data);
   $data = mysqli_real_escape_string($con, $data);
   $data= htmlspecialchars($data);

Note: htmlspecialchars function shoud be after mysqli_real_escape_string not before like in the example provided by you.

Check the output of the return statement of the function as well.

FreakingOUT · February 5, 2014

Where are you defined a $con variable as a first parameter to mysqli_real_escape_string() function?
$call = mysqli_real_escape_string($con,$call);
Oh, because when Barand suggested using it, this was the format I found on a w3schools.com page!

-FreakingOUT

FreakingOUT · February 5, 2014

Where are you defined a $con variable as a first parameter to mysqli_real_escape_string() function?
$call = mysqli_real_escape_string($con,$call);
Oh, because when Barand suggested using it, this was the format I found on a w3schools.com page!

-FreakingOUT

Also, in order to escape characters the proper order shoud be:
 // Sanitize message input
   $data = stripslashes($data);
   $data = mysqli_real_escape_string($con, $data);
   $data= htmlspecialchars($data); 
Note: htmlspecialchars function shoud be after mysqli_real_escape_string not before like in the example provided by you.

Check the output of the return statement of the function as well.

Thanks - will try tha, but not sure what you mean "Check t he output of the return statement of the function as well", as that seems to be working.

-FreakingOUT

FreakingOUT · February 5, 2014

Thanks - will try tha, but not sure what you mean "Check t he output of the return statement of the function as well", as that seems to be working.

-FreakingOUT

"DANGER, DANGER, WILL ROBINSON" ... tried your Sanitize function change, but now it yields an error, so am going back to what worked !!!

-FreakingOUT

mac_gyver · February 5, 2014

several points about your sanitize function.

1) trimming data should be something you do depending on what the data is and how it will be used.

2) you should ONLY use stripslashes on incoming data if php's magic_quotes_gpc setting is on. unconditionally using it will prevent actual \ characters in the data.

3) htmlspecialchars or htmlentities should ONLY be used when you output data to the browser, not when the data is input.

4) you should only escape functions on string data and ONLY right before you use it in a sql query statement.

5) numerical data needs to be validated/cast as the appropriate numerical type. using an escape string function on numerical data, when it is used correctly in the sql query statement, won't prevent sql injection.

FreakingOUT · February 5, 2014

Also you need to sanitize the post data with mysqli_real_escape_string() and not with htmlentities() prior to using it in the query. Or use a prepared statement.

Check data has been posted before attempting to query and output the table

I went back and reviewed Barand's comment "Check data has been posted before attempting to query and output the table". Not sure if I understand this correctly, but decided to try and eliminate the premature "No records found" by putting the <form> part at the top of the page.

Ran the code through a code checker which showed no errors, but when the page first loads (without doing anything else), I now get this:

?> No records found

The latest attempt is below.

-FreakingOUT

<?php

?>

<html>
<head><title></title>
</head>
<body>

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
CALL: <input type="text" name="call"> <input type="submit">
</form>
</body>
</html>

?>

<?php

// define variables and set to empty values

$call = "";

if ($_SERVER["REQUEST_METHOD"] == "POST")
{
  $call = test_input($_POST["call"]);
}

function test_input($data)
{
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

require '/myserverpath/kqr_mysqli.php';

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$call = mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

$result = mysqli_query($con,$sql);

if (!$result) echo mysqli_error($con);

if (mysqli_num_rows($result) == 0 ):
    echo "No records found<br>";
else:
if (mysqli_num_rows($result) >= 1 ):
echo "<div style ='font:11px Arial,tahoma,sans-serif;color:#ff0000'>";
echo "<table border='1' cellpadding='3' cellspacing='3'>
<tr>
<th>CALL</th>
<th>DATE</th>
</tr>"; 
while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
	echo "<td>" . $row['call'] . "</td>";
	echo "<td>" . $row['qsodate'] . "</td>";
}
endif;
echo "</table>";
echo "</div>";

endif;

mysqli_close($con);

?>

jazzman1 · February 5, 2014

3) htmlspecialchars or htmlentities should ONLY be used when you output data to the browser, not when the data is input.

I have to disagree with this, but...it's too early (6:30 AM) to explain why

FreakingOUT · February 5, 2014

several points about your sanitize function.

1) trimming data should be something you do depending on what the data is and how it will be used.

2) you should ONLY use stripslashes on incoming data if php's magic_quotes_gpc setting is on. unconditionally using it will prevent actual \ characters in the data.

3) htmlspecialchars or htmlentities should ONLY be used when you output data to the browser, not when the data is input.

4) you should only escape functions on string data and ONLY right before you use it in a sql query statement.

5) numerical data needs to be validated/cast as the appropriate numerical type. using an escape string function on numerical data, when it is used correctly in the sql query statement, won't prevent sql injection.

Thank you, mac_gyver. Will have to check with my ISP about the magic_quotes_gpc setting, but it's 3:55AM (local) right now so will have to do this after they open. Only 2 hours of sleep here, so I'm going back to bed {SIGH}.

The form entries will never contain a backslash - ONLY A-Z and 0-9 with perhaps an occassional "/" (forward slash) in the 'CALL'.

I'll re-read and chew on your info again after I get more SLEEP.

-FreakingOUT

Barand · February 5, 2014

No need to contact your provider the get_magic_quotes_gpc function will tell you.

if (get_magic_quotes_gpc()) {
    $data = stripslashes($data);
}

mac_gyver · February 5, 2014

by using htmlspecialchars or htmlentities on data being put into a query statement, the data is altered.

for this specific example of a search value, that would require that the data that is stored in the database to also have htmlspecialchars or htmlentities applied to it. for doing things like bulk importing of data using a load data infile query, that's not possible.

short-answer: data should be stored as the actual unaltered data value so that it can be used in any context, not just to be output on a html web page.

the conditional suggestion for the stripslashes was to do this in code -

// Usage across all PHP versions
if (get_magic_quotes_gpc()) {
    $some_variable = stripslashes($some_variable);
}

mac_gyver · February 5, 2014

this is the pseudo code that Barand handed you -

if a call value was posted
    query table
    if no records
        display no records
    else
        display results
    end if
end if

this is the implementation of that logic -

// this assumes you have done any filtering/validation of the $call value prior to this
if($call == ''){
    echo "The search term is empty.";
} else {
    // a non-empty search term was submitted
    $call = mysqli_real_escape_string($con,$call);
     $sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";
     $result = mysqli_query($con,$sql);
     if (!$result){

        die(mysqli_error($con); // there's no point in producing follow on errors if the query failed due to an error
    }
 
    if (mysqli_num_rows($result) == 0 ){
        echo "No records found<br>";
    } else {
        // one or more records were found

        // output your table heading here....

        // loop over the row(s) the query returned
        while($row = mysqli_fetch_assoc($result)) {

            // output the table data here...
        }
        
        // end your table here...
    }
}

jazzman1 · February 5, 2014

Well mac, I'm aware what php htmlspecialchars function does, but we still don't know how looks the searchable value(s) in his column.
Also, many web developers escape the html spacial characters when they insert a data to database and there is nothing wrong in this method,personally not use.
Let's doing a simple test:
Assuming, we have two records in the XSS table, first record is escaped when the message being inserted, the second one - no.

mysql> select msg from XSS;
+--------------------------------------------------------------------------------------------------------------------------+
| msg                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------+
| <a href="#" onclick=javascript:window.location.href='http://phpfreaks.com'>Click Me!</a> |
| <a href="#" onclick=javascript:window.location.href='http://phpfreaks.com'>Click Me!</a>                                 |
+--------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.10 sec)

1.
// Sanitize message input for first inserted row
$msg = stripslashes($msg);
$msg = mysqli_real_escape_string($msg);
$msg = htmlspecialchars($msg, ENT_QUOTES,'UTF-8');

2.
// second method without using htmlspecialchars
$msg = stripslashes($msg);
$msg = mysqli_real_escape_string($msg)

So, now if I want to find how many records have a column msg with values starting with "<a href=" my query/php script would go something like this

<?php

$username = '***';

$password = '***';

$database = '***';

$dbname = 'XSS';

$target = '<a href=';

function strip_data(&$data, $con) {
    if (is_array($data)) /* in case you have an array of strings*/ {
        array_walk($data, "strip_data");
    } else {

        // Usage across all PHP versions
        if (!ini_get(get_magic_quotes_gpc())) {
            $data = stripslashes($data);
        }
        // Sanitize target searchable data
        $data = stripslashes($data);
        $data = mysqli_real_escape_string($con, $data);
        $data = htmlspecialchars($data, ENT_QUOTES, 'UTF-8');
    }
    return $data;
}

//conection:
$link = mysqli_connect($database, $username, $password, $dbname) or die("Error " . mysqli_error($link)); // use your database credentials

// escape the data
$msg = strip_data($target, $link); 

//consultation:
$query = "SELECT msg FROM test.XSS WHERE msg LIKE '$msg%'" or die("Error in the consult.." . mysqli_error($link));

//execute the query.
$result = mysqli_query($link, $query);

if (!$result)
    die(mysqli_error($link));

// count the number of rows in a result set
$count = mysqli_num_rows($result);

if ($count == 0) {
    echo "No records found<br>";
} else {
    echo 'one or more records were found';
    // output your table heading here....
    // loop over the row(s) the query returned
}

The result of target string is:

one or more records were found

So, I don't want to say that that issue is like in my example, but we could use a htmlspecialchars function when the data is input.

Edited February 5, 2014 by jazzman1

FreakingOUT · February 7, 2014

Had to take a break from this to study for a license renewal exam yesterday afternoon. Fortunately, I passed {SIGH}.

Now if I can just get "passed" (PUN) my mental block with this PHP/MySQL matter, life will be roses. Well, almost.

Barand had originally pointed out:

<snip>CALL is a mysql reserved word, so you need to enclose it in backticks

That fly in the ointment has been resolved for the basic db query to read data already in the MySQL Table, however tied into the PHP script to read the data, is another scrip that first must parse a weird formatted data file prior to INSERTING the data into the MySQL Table (for my initial test I did it manually with phpMyAdmin.

I found a custom PHP Class that parses the weird .adi file data OK, except that the first field in the file key value for the array is that same word again: "CALL" ;-(

Fortunately, this section of the display code outputs the contents of "CALL" in the browser OK:

<?php

// Basic schema to Parse an ADIF (.adi) file into manageable data for input to MySQL qsolog Table

include 'adif_parser.php';

$p = new ADIF_Parser;

$p->load_from_file("test.adi");
$p->initialize();

while($record = $p->get_record())
{
	if(count($record) == 0)
	{
		break;
	};
	echo $record["call"]."<br>";

};
?>

I've tried all sorts of variations of backticks (real ones this time), single & double-quotes, but still can't seem to find the Magic Silver Bullet for adding the "INSERT INTO" (i snipped out all the MySQLconnection stuff) and without getting syntax errors:

<snip>

$sql = "INSERT INTO `qsolog` (`call`) VALUES (`$record["call"]`);

mysqli_query($con,$sql);

<snip>

Since there will be 8 total fields of data involved, I'd like to try an shorten all the VALUES involved to ones like: '$call', etc. for the $sql line and axe using the longer '$record[ ]' part.

Trying:

$call == '$record["call"]';

and other variation attempts haven't worked either.

{SIGH}.

-FreakingOUT

Barand · February 7, 2014

Don't put strings in backticks, only identifiers (tablename, columnname etc). String values need single quotes. Because the string variable in this case is an array reference use curly brackets too

$sql = "INSERT INTO `qsolog` (`call`) VALUES ('{$record["call"]}' );

FreakingOUT · February 7, 2014

Don't put strings in backticks, only identifiers (tablename, columnname etc). String values need single quotes. Because the string variable in this case is an array reference use curly brackets too
$sql = "INSERT INTO `qsolog` (`call`) VALUES ('{$record["call"]}' );
Thanks, Barand... I tried this but still get an Error:
PHP Syntax Check: Parse error: syntax error, unexpected '"', expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)

-FreakingOUT

boompa · February 7, 2014

The problem is you have a " inside another ". You have a few options. One of these is to escape, via a backslash (\), the double quotes within the outer double quotes (and you're missing the last double quote too), like this:

$sql = "INSERT INTO `qsolog` (`call`) VALUES ('{$record[\"call\"]}' )";

You could also break the string up like so:

$sql = "INSERT INTO `qsolog` (`call`) VALUES ('" . $record["call"] . "' )";

Also, where you're already inside of quotes, I believe you can forgo the quotes around the array key of call, like so:

$sql = "INSERT INTO `qsolog` (`call`) VALUES ('{$record[call]}' )";

Barand · February 8, 2014

Boompa, FYI

Despite the " within another ", this code runs fine (The curlies take care of the inner " )

$record["call"]='abc';

$sql = "INSERT INTO `qsolog` (`call`) VALUES ('{$record["call"]}')";
$db->query($sql) or die($db->error);

$sql = "SELECT id, `call` FROM qsolog";
$res = $db->query($sql);
$row = $res->fetch_assoc();
vprintf ("ID : %d<br>CALL : %s", $row );

/* OUTPUT ***
ID : 1
CALL : abc
*/

FreakingOUT · February 8, 2014

Also, where you're already inside of quotes, I believe you can forgo the quotes around the array key of call, like so:
$sql = "INSERT INTO `qsolog` (`call`) VALUES ('{$record[call]}' )";
WOW ... this works !!! It's the shortest, but if you have another jewel in your bag of tricks there to further shorten what will be multiple VALUES, that would be great.

Thanks very much for "making my day" already !!!

-FreakingOUT

FreakingOUT · February 8, 2014

OOOPS... correction...

This is the one that works:

$sql = "INSERT INTO `qsolog` (`callsign`) VALUES ('" . $record["call"] . "' )";

-FreakingOUT

FreakingOUT · February 8, 2014

Three questions:

1. Each record fed into an array by the Parser will yield 8 fields (columns) of data totalling about 50 characters per record. So if I upload & parse 5,000 records in one batch, do you think that is doable? I guess it also depends on my ISP's server & RAM, etc.? There is a possibility at some point a particular file *could* be as large as 20,000 records... or in one case, even 50,000 records, but those would be the exceptions.

2. The Date field data comes out of the Parser like "20140208", instead of the "2014-02-08" format I had planned to use. However, there will be a need for users to verify a MM and DD for specific records, so is one format flavor more desirable for this purpose than the other?

3. The Time field data comes out of the Parser like "181519" (HHmmss), and the same question .... would this be easier for time similar verification purposes than "18:15:19" (the acceptable range would be within one-hour either side of what is in the field) ??? Where things get dicey is if the Parser output Date/Time is something like 2013-12-31 & 23:45:15, but for verficiation purposes (if someone's clock was off by ~ one hour), would need to accept 2014-01-01 & 00:44:14 (if I mentally and on fingers did that right :^)

Thanks very much.

-FreakingOUT

Sign In

BOOLEAN WHO ? (FROM WHERE Headbanger)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information