HTTP AUTH and SQL WHERE Clause

[crseader]

Hey Folks,
I am having trouble with some variables the $HTTP_AUTH_USER and $HTTP_AUTH_PW - now im useing PHP 4.3.3 and so i know that you have to use $SERVER['HTTP_AUTH_USER'] and $SERVER['HTTP_AUTH_PW'] instead of the old way now. Well i am having trouble with this script i have created to authenticate user name and password and verify against a postgresql database below.

$auth = false; // Assume user is not authenticated

if (isset( $SERVER['PHP_AUTH_USER'] ) && isset($SERVER['PHP_AUTH_PW'])) {

// Connect to MySQL 

pg_pconnect("host=172.18.204.64 port=5432 dbname=acquisuite_db user=pgadmin password=pgadmin") or die ( 'Unable to connect to server.' ); 

// Select database on MySQL server 

// mysql_select_db( 'your_db' )
// or die ( 'Unable to select database.' );

// Formulate the query 

$sql = ("SELECT * FROM tbl_authenticate WHERE username = '$PHP_AUTH_USER' AND password = '$PHP_AUTH_PW'"); 

// Execute the query and put results in $result 

$result = pg_exec( $sql ) 
    or die ( 'Unable to execute query.' ); 

// Get number of rows in $result. 

$num = pg_num_rows( $result ); 

if ( $num != 0 ) { 

    // A matching row was found - the user is authenticated. 

    $auth = true; 

} 

}

The Problem is on the $sql line when i put in the string to do the Query with the WHERE clause having the $HTTP_AUTH_USER and $HTTP_AUTH_PW. When i change it to '$SERVER['HTTP_AUTH_USER']' and '$SERVER['HTTP_AUTH_PW']' it does not work and i get a parse error.
How can i get around this?
does anyone have any ideas for me.
Thanks

Cameron

[onyx]

What was the error? An error in the php or mysql?

try this:

$user = $SERVER['HTTP_AUTH_USER'];
$pass = $SERVER['HTTP_AUTH_PW'];
$sql = ("SELECT * FROM tbl_authenticate WHERE username='$user' AND password='$pass'");

What happens then?

[crseader]

ah yes thank you ill try that -
why didn't i think of that 🙂

Thanks again ill let you know if it works

Cameron

[crseader]

well that did not work i am still getting an error 401 not authorized on my script here. : here is the rest of the script if you care to help me out.

<?php

ob_start(); // cache header/body information, send nothing to the client yet.
// we actually keep cacheing until the end of the script, if you have a large amount of data,
// this could potentially make your server run out of memory. Use ob_end_flush()
// to flush the output cache and send the headers, however "ReportFailure()"
// won't work after ob_end_flush()

function ReportFailure($szReason, $RA, $SERIALNUMBER, $MBIP, $PORT, $M😎
{ Header("WWW-Authenticate: Basic realm=\"UploadRealm\""); // realm name is actually ignored by the AcquiSuite.
Header("HTTP/1.0 406 Not Acceptable");
$szNotes = sprintf("Rejected logfile upload from %s Serial %s, modbus ip %s:%s, modbus device %s.", $RA, $SERIALNUMBER, $MBIP, $PORT, $M😎;
printf("FAILURE: $szReason\n"); // report failure to client.
printf("NOTES: $szNotes\n");
ob_end_flush(); // send cached stuff, and stop caching. we've already kicked out the last header line.

syslog(LOG_ERR, "$PHP_SELF: $szNotes   Reason: $szReason \n");
exit;    

}

function ReportAuthorizationFailure($szReason, $RA, $SERIALNUMBER)
{ Header("WWW-Authenticate: Basic realm=\"UploadRealm\""); Header("HTTP/1.0 401 Unauthorized");
$szNotes = sprintf("Rejected logfile upload from %s Serial %s.", $RA, $SERIALNUMBER);
printf("FAILURE: $szReason\n"); // report failure to client.
printf("NOTES: $szNotes\n");
ob_end_flush(); // send cached stuff, and stop caching. we've already kicked out the last header line.

syslog(LOG_ERR, "$PHP_SELF: $szNotes   Reason: $szReason \n");
exit;    

}

function GetFileChecksum($szFilename)
{
$szChecksum = "[error]"; // provide some default value for this incase the following fails.
if (file_exists($szFilename))
{ $szCommand = sprintf("/bin/busybox md5sum -b %s", $szFilename); // busybox software provides command line MD5 checksums.
$rfp = popen($szCommand, r); // execute busybox
if ($rfp) { $szBuffer = fgets($rfp, 64);
list($szChecksum, $junk) = split(" ", $szBuffer, 2); //truncate after MD5 checksum. Space follows it prior to file name.
pclose($rfp);
} // end if rfp not null.
else
{ printf("/bin/busybox md5sum not found or will not execute. Checksum can not be calculated.");
syslog(LOG_ERR, "$PHP_SELF: /bin/busybox md5sum not found or will not execute. Checksum can not be calculated.");
}
}
return $szChecksum;
}
function ProcessLogFileUpload($REMOTE_ADDR, $SERIALNUMBER, $MODBUSIP, $MODBUSPORT, $MODBUSDEVICE, $LOGFILE, $MODBUSDEVICENAME, $MODBUSDEVICETYPE, $MODBUSDEVICETYPENUMBER, $MODBUSDEVICECLASS) {
$nSuccessCount = 0; // set counters to zero.
$nDuplicateCount = 0;
$nRejectCount = 0;

$fd = gzopen($LOGFILE, "r");
if (! $fd)
{ ReportFailure("Can't open logfile. gzopen($LOGFILE) failed.", $_SERVER['REMOTE_ADDR'], $SERIALNUMBER, $MODBUSIP, $MODBUSPORT, $MODBUSDEVICE);
exit;
}
else
{ while(!gzeof($fd))
{ $szBuffer = gzgets($fd, 512);
if (strlen($szBuffer) > 15) // 15 is kinda arbitrary, use enough space for date, err and alarms. 15 characters should be fine.
{

$tmpArray = split(",", $szBuffer); $nCol = 0;
$query = "INSERT INTO sometable VALUES ("; // query prefix. finished query string is: insert into table values ('1','2','3')
foreach ($tmpArray as $value) // loop through each array element by calling it "value", (one per data column,)
{ $value = str_replace("\"", "", $value); // strip double quotes
$value = str_replace("'", "", $value); // strip single quotes
$value = str_replace(";", "", $value); // strip semicolon. (not needed if you use the following MySQL command)
$value = trim($value); // trim whitespace, tabs, etc, on the ENDS of the string.

// $value = mysql_escape_string($value); // MySQL has a special strip function just for this purpose, other SQL versions may vary.
switch($nCol)
{ case 0: $query = $query . sprintf("'%s'", $value); break; // quote data (utc date), first column has no leading comma
case 2:
case 3: $query = $query . sprintf(",%s", $value); break;

{ if ($value == "NULL") $query = $query . ",NULL" ; // don't quote the word 'NULL'
else $query = $query . sprintf(",'%s'",$value); // quote data, all other columns need leading comma seperator.
}
}
$nCol++;
}
$query = $query . ")"; printf("SQL: %s \n", $query);

             // again, this query doesn't actually execute in the sample. Replace with a query apporpriate for your flavor of SQL
             // $result = sql_query($query,  $SQL)                 
             $result=1; // force success code here, REMOVE THIS LINE.
             if (!  $result  )
             {   // check error message from SQL query, ignore duplicate warnings.	
             	// $strErrorMessage =     sql_error($SQL);
                  if (stristr($strErrorMessage, "duplicate entry") != FALSE)
                  {   $nDuplicateCount +=1;                  	 
             	 }
             	 else
             	 {   $nRejectCount +=1;
                  }
             	 printf( "WARNING: line rejected, %s: DATA: %s\n", $strErrorMessage, $szBuffer);
             }
             else                 
             {    $nSuccessCount += 1;
			 }
        }
        else
        {    printf( "WARNING: short line rejected. DATA: %s\n", $szBuffer);
             $nRejectCount +=1;
		}
	}
   gzclose($fd);
   // PHP automatically removes temp files when the script exits.

}
// now that the data is in the database, check the most current data point, and calculate the alarm status.
// query for last record in the database, get device status,

echo "REPORT: Uploaded data file contained $nSuccessCount vaild records, $nDuplicateCount duplicate records, and $nRejectCount rejected records.\n";

// Finally, send the result code back to the AcquiSuite. Success cause the log file to be purged.
//$nRejectCount = 1; // for this sample script, the nRejectCount is set to 1 to prevent the AcquiSuite from flushing the log file. YOU SHOULD REMOVE THIS LINE.
if ($nRejectCount != 0)
{ ReportFailure( "An error occurred while importing data from the data file.\n", $_SERVER['REMOTE_ADDR'], $SERIALNUMBER, $MODBUSIP, $MODBUSPORT, $MODBUSDEVICE);
}
else
{ echo "SUCCESS\n";
}
} // ProcessLogFileUpload
function SendConfigFileManifest($REMOTE_ADDR, $SERIALNUMBER, $MODBUSIP, $MODBUSPORT)
{
// printf("CONFIGFILE,loggerconfig.ini,%s,%s\n", $szConfigurationChecksum, $szConfigurationChangeTime);

// for each modbus device on this acquisuite, process the following line:
// printf("CONFIGFILE,modbus/mb-%03d.ini,%s,%s\n", $nModbusAddress, $szConfigurationChecksum, $szConfigurationChangeTime); 

}

function SendConfigFile($REMOTE_ADDR, $SERIALNUMBER, $MODBUSIP, $MODBUSPORT, $MODBUSDEVICE, $MODBUSDEVICECLASS)
{
}
/
if ($SENDDATATRACE == "YES") printf("<pre>\n");// make the output more readable
//$conn_string = "host=172.18.204.64 port=5432 dbname=acquisuite_db user=pgadmin password=pgadmin";
//$dbconn = pg_connect($conn_string);
if(!isset($SERVER['PHP_AUTH_USER']))
{ ReportAuthorizationFailure("A system serial number must be supplied to upload data\n", $SERVER['REMOTE_ADDR'], $SERVER['PHP_AUTH_USER']);
exit;
// if your server terminates here, please read PHP Authentication comments above.
}
if(!isset($SERVER['PHP_AUTH_PW']))
{ ReportAuthorizationFailure("A system serial number and password must be supplied to upload data\n", $SERVER['REMOTE_ADDR'], $SERVER['PHP_AUTH_USER']);
exit;
// if your server terminates here, please read PHP Authentication comments above.
}
if ($SERIALNUMBER != $SERVER['PHP_AUTH_USER'])
{ ReportAuthorizationFailure("SerialNumber and http authentication username do not match.", $SERVER['REMOTE_ADDR'], $SERVER['PHP_AUTH_USER']);
exit;
}
$auth = false; // Assume user is not authenticated
if (isset( $SERVER['PHP_AUTH_USER'] ) && isset($SERVER['PHP_AUTH_PW'])) {
pg_pconnect("host=172.18.204.64 port=5432 dbname=acquisuite_db user=pgadmin password=pgadmin") or die ( 'Unable to connect to server.' );
$user = $SERVER['HTTP_AUTH_USER'];
$pass = $SERVER['HTTP_AUTH_PW'];
$sql = ("SELECT FROM tbl_authenticate WHERE username = '$user' AND password = '$pass'");
$result = pg_exec( $sql )
or die ( 'Unable to execute query.' );
// Get number of rows in $result.
$num = pg_num_rows( $result );
if ( $num != 0 ) {
// A matching row was found - the user is authenticated.
$auth = true;
}
}
if ($SERVER['PHP_AUTH_PW'] != "password" )
{ ReportAuthorizationFailure("The authentication serial number password do not match.", $SERVER['REMOTE_ADDR'], $SERVER['PHP_AUTH_USER']);
printf("Hint: this sample script uses 'password' as the passowrd");
exit;
}

// ok, we got this far, we must have had a valid serial number and password.

Thanks,
Cameron Seader

[onyx]

Have you tried printing out the username and password variables in question to determine if they are indeed correct? If it was working correctly before and that is the only thing you changed then it would point to a problem with the variable not being set.

[crseader]

how? how do i do that.
either the query that i setup to retreive and verify the user and pass is not correct or the device that is sending the user and pass is not sending it, which i know it is sending it. so i guess its my query.

ill have to try some more i guess. i don't know what else to try.

There are two variables passed to this script in the form of $HTTP_AUTH_USER and $HTTP_AUTH_PW from another machine and this script that im playing with reads them in and authenticates it based on if they match whats in the postgresql database from the query that i wrote in here. then after it is authenticated it sends some log files that get read into the database.

I have never been able to get this authentication to work.
do you suppose that it would matter that the other machine uses an older version of php than the one i use which is 4.3.3. Would the variables being passed to the php script be a problem because of that?
just a thought.

I appreciate your thoughts, it makes it nice to chat with someone that knows how to write in PHP about this problem im having.
Thanks,
Cameron