PDO help please

StephenL

Ok I'm trying to get my head around PDO to make prepared statements. Here is the code that I am using to test it.


try {
    $dbh = new PDO('mysql:host=localhost;dbname=db', $user, $pass);

}catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}



	//Make the prepared statment
 $stmt = $dbh->prepare('SELECT userName,Password FROM members WHERE userName = ? AND Password = ?');

 $stmt->execute(array($_POST['username'],$_POST['password']));
	 //There is a user so check to see if pass is correct.
	  $row = $stmt->fetchAll();

	 print_r($row);
	 echo "<br>HELLOOOO";
	 echo $row['Password'];
	 if ($row['Password'] == $_POST['password']) {
		 //The password is correct so reg. sessions
		 $_SESSION['user'] = $row['userName'];			 
		 $client = md5($_SERVER['HTTP_USER_AGENT']);
		 header("Location: newslist.php");


}

When the form passes the information I see the print_r($row) array but the if statement does not run. I then added the HELLOOO as a place holder (and to let out stree) tried out echo out the value of $row['Password']. For some reason this is not set even though I can see the array printed out above it.

Can anybody tell me maybe what I am doing wrong?

Thanks

Stephen

NogDog

$row[0]['Password']

fetch_all() retrieves a 2-D array with reach result row being the 1st dimension.

PS: Assuming you only expect to get one result row, it might make more sense to use fetch() instead, in this case, and just retrieve one result row as a 1-D array.

StephenL

Ok did some fooling around and I found that it is going into a multi array? does anybody have any idea why?

I swaped

 echo $row['Password'];

with

echo $row[0][0];

and now I am seeing what I was looking for but why is in coming back like this, any help would be great.

Stpehen

StephenL

Thanks man, thanks really want I wanted to here. I guess I missed your post or was in writing my other response while you posted.

Again thank for the help, so would this kind of login stop injection?

NogDog

Any values used in prepared statement place-holders will automatically be escaped as needed, so should obviate any SQL injection issues with regard to those values.

StephenL

Ok I just found out that the server I'm working for is running PHP 4.3.9 and support for PDO is not there. So the only options that I have no to protect against injection would be to use mysql_escape_string() on the info I am passing to the sql, this correct?

Stephen

laserlight

StephenL wrote:
Ok I just found out that the server I'm working for is running PHP 4.3.9 and support for PDO is not there. So the only options that I have no to protect against injection would be to use mysql_escape_string() on the info I am passing to the sql, this correct?

If you are using MySQL and if you are dealing with strings. If you are dealing with an integer instead, you would likely want to cast the variable to integer instead.

Of course, there might exist other options, like campaigning for an upgrade of PHP in view that PHP 4.3.9 has been officially obsolete for many months.

StephenL

So I re-wrote the PDO function I have to make it work with the PHP version they have. I also have put in a request for them to upgrade there PHP to 5.3.0.

Ok would this function protect against SQL injection?

function main_login($userName,$Password) {
	//Include the DB connection info.
    require_once('db.php');

//escape values and stip them to stop injection
$user = strip_tags($userName);
$user = mysql_escape_string($userName);
$pass = strip_tags($Password);
$pass = mysql_escape_string($Password);

//Get user information
$sql = "SELECT * FROM members WHERE userName ='$user'";
$result = mysql_query($sql);

//Check to see if there was a match
$numRow = mysql_num_rows($result);

if ($numRow == 0) {
	//There was no match so post message
    echo "<p>There was an error logging you in please try again</p>";
    include('login.inc');
}else{
    //There is a match to test the password against that in the DB
    //Get the password from the DB
    $row = mysql_fetch_array($result);

    if ($row['Password'] == sha1($pass)) {
	    //Password matched the DB start session and load values and redirect to first page.
	    $_SESSION['user'] = $row['userName'];			 
	    $salt = $_SERVER['HTTP_USER_AGENT'];
        $salt .="_saltWord";
        $_SESSION['fingerprint'] = md5($salt);
        header("Location: firstPage.php");
    }else{
	    //The Password didn't match so redirect them back to the login page with error message.
	    header("Location: login.php?error=1");
    }
}
}//End Function

I was also wondering if you could put the stip_tags() and mysql_escape_string() on one line..

$user = mysql_escape_string(strip_tags($userName));

Thanks again for all your help, I hope I am not making peoples head ache 🙂

Stephen

NogDog

mysql_real_escape_string() (the better option than mysql_escape_string(), but requires that your MySQL connection be established firs), is all that is needed to prevent SQL injection. Anything else you add (strip_tags(), etc.) is for any other non-SQL-related filtering/escaping that you decide you want to do for other reasons.

And yes, you can chain multiple functions together that each return the modified value.

NogDog

PS: You could use your own sanitizing function along with sprintf() to do something similar to the prepared statement:

function sanitize($value)
{
   if(!is_numeric($value))
   {
      if(get_magic_quotes_gpc())
      {
         $value = stripslashes($value);
      }
      $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

$user = (!empty($_POST['user'])) ? trim($_POST['user']) : '';
$pwd = (!empty($_POST['pwd'])) ? trim($_POST['pwd']) : '';

$db = mysql_connect('blah', 'blah', 'blah');
mysql_select_db('blah');
$sql = sprintf(
   "SELECT * FROM users WHERE username = %s AND password = %s",
   sanitize($user),
   sanitize($pwd)
)
$result = mysql_query($sql);

laserlight

NogDog wrote:
mysql_real_escape_string() (the better option than mysql_escape_string(), but requires that your MySQL connection be established firs), is all that is needed to prevent SQL injection.

Oops, I missed the missing escape 🙂

NogDog wrote:
You could use your own sanitizing function along with sprintf() to do something similar to the prepared statement:

However, one difference between a placeholder in a prepared statement and a format specifier with sprintf() is that the prepared statement handles the data according to the type with respect to SQL/the database, but the format specifier handles the data according to the type with respect to PHP.

Hence, where a query like:

"SELECT * FROM users WHERE username = :username AND password = :password"

is fine, this:

"SELECT * FROM users WHERE username = &#37;s AND password = %s"

should be:

"SELECT * FROM users WHERE username = '%s' AND password = '%s'"

NogDog

laserlight;10930129 wrote:

... this:

"SELECT * FROM users WHERE username = %s AND password = %s"

should be:

"SELECT * FROM users WHERE username = '%s' AND password = '%s'"

Take a closer look at my sanitize() function. 😉 (It adds the quotes for non-numeric values.)

laserlight

NogDog wrote:
Take a closer look at my sanitize() function. (It adds the quotes for non-numeric values.)

The fact that I had to take a closer look means that it is probably not a good idea. You should more completely emulate prepared statements instead of using it in conjunction with sprintf(), otherwise not do that and let the user control with sprintf(). Also, it actually performs a kind of manifest typing, which may be a good thing, but strictly speaking is not correct in terms of the static typing of standard SQL.