The error I'm getting is: Fatal error: Call to a member function execute() on a non-object in C:\wamp\www\Texaco\mysql_injection.php on line 13

I'm trying to use this code to parameterize my email and password variables in order to avoid SQL Injection. Every example is using an OOP approach and I'm still doing procedural, so I need to get up to speed on this.

Who is Participating?

//Connect to the DB$db = new PDO("mysql:host=localhost;dbname=yourDatabase", 'username', 'password');//Prepare a select statment with 'named' parameters$stmt = $db->prepare("SELECT email, first_name FROM registration where email = :email and contestant_password = :password");//setup the data to be passed as the named parameter$data = array( 'email' => trim($_POST['email']), 'password' => trim($_POST['password']));//execute the query along with the data$stmt->execute($data);//if a result was returned (i.e there was a match) then assign that record to $rowif ($row = $stmt->fetch(PDO::FETCH_OBJ)) { //set some variables to values returned from the $row $_SESSION['sv_email'] = $row->email; $contestant_email = $row->email; $contestant_first_name = stripslashes($row->first_name);} else { //No Match Found}$link = "contestants_homepage.php";

Apparently, by setting up the select statement that way, any rogue code that hacker is attempting to insert into my select statement will be rendered harmless. At least, that's what I've been able to understand thus far based on what I've read (feel free to correct and / or add your input at any time).

My OOP chops are minimal, still this is a good opportunity to learn something besides a concept aimed at preventing SQL Injection, so...

How should that query look given the fact that I'm intentionally trying to test it given the scenario where my user / dirtbag has intentionally inserted ''or 1=1-- as their password in order to access my database?

Using prepared and bound statements eliminates injection - you do not need to worry about it (unless someone comes up with a way to crack it - in which case it would just need updating)

So you are trying to prevent something that cannot happen - stop writing your code the old way (MySQL_query)

With prepared statements mysqli and pdo send the information separately - it sends the statement part (select from) and then sends the data part ($email,$password etc)
The sql statement and data are not joined in the way MySQL_query did it.

As Gary has already said, you're thinking too old-school. With mySQLi and PDO prepared statements you can't possibly end up with:

SELECT * FROM table WHERE password = "password" OR 1=1;

That said, you have some errors in your code that would prevent it from running anyway. The execute() method doesn't take arguments (the PDO version does!!). You need to bind the arguments before executing. As you are only check if a result exists, you will only ever return 1 record, so tweak it accordingly:

$email="bruce@brucegust.com";$password = "'or '1=1--";if ($stmt = $mysqli->prepare("SELECT count(email) AS total FROM registration where email = ? and password= ? LIMIT 1")) { $stmt->bind_param("ss", $email, $password); //bind the data to the query $stmt->bind_result($total); //bind the result to the $total variable $stmt->execute(); //execute the query $stmt->fetch(); //fetch the result = it will only ever be 1 or 0 if ($total) { echo "We have a match"; } else { echo "We don't have a match"; }; $stmt->close(); }

First off, thanks for your time. Rather than just copying and pasting, I want to try and explain back to you what it is that you're suggesting I do and why it works the way that it does.

PDO stands for PHP Data Objects. It's a relatively new addition to the PHP family and it allows for a more secure and organized approach to querying a database - something that's especially relevant when you're trying to code in a way that prevents SQL Injection.

When looking at the code that initially started with, it was fundamentally flawed in that I was attempting to combine a procedural dynamic with an OOP / PDO approach.

PDO is a class. A class is like a football team and the object(s) are the individual players and their respective rolls. The code that you've documented above is combing the PDO class with OOP approach to coding.

The difference between OOP and procedural programming is that with OOP, instead of every piece of data being generated by a standalone query, I now have the option of grabbing the same information from one query written only one time. In other words, there's an "object" being referred to for the information rather than a solitary query that, hypothetically may have to be written and re-written multiple times within a single page.

With PDO, I'm taking things a step further in that I'm introducing an additional step in that rather than a traditional query, I'm "preparing" that query by establishing some basic scaffolding and then I'm going to "bind" my variables to that query.

What's cool about all this, apart from it being a more efficient and organized approach, is that my variables are being processed differently than if I were running the same query using a procedural dynamic.

With a procedural approach, something sinister like ' or 1=1 is going to be processed as a part of the query itself, hence a hacker's ability to access my database. But by using PDO, because of the way the variables are being bound to the query, they'll be processed as simple text and a hacker will fail to trigger a SQL command.

Is that correct?

0

brucegustPHP DeveloperAuthor Commented: 2013-10-10

Gary, what is "ss" in $stmt->bind_param("ss", $email, $password); //bind the data to the query

Yes you have got it. The scaffolding is a good analogy - once the query is prepared it can be used multiple times.
Where this comes into its own would be if you were executing thousands of queries - all you need to do is set your bound variables and execute, you don't need to prepare the statement again and again and again.
Also remember even though you can do the binding and execution separately for each query you can also bind multiple values (think multiple rows of queries - a hundred inserts) and execute in one go - super fast.
When I transferred to PDO I took an sql processing page from 15 minutes to 2 minutes - this was running thousands of queries.

You've kind of got it. In PHP you use a library to access the mySQL database. The big three are mysql, mysqli and PDO. mysql is being dropped (deprecated) so that leaves you with the 2.

The mysqli library can be used like the old mysql library in a procedural, but it can also be used in an Object Oriented manor.

PDO is purely an Object Oriented library. The code we've been using is the mysqli library, but done in the object way. PDO is very similar.

The libraries are classes, that once initiated become objects. Think of a class as a blueprint, and the object is the 'thing' created from the blueprint. Once you create an object you can execute lots of in-build methods (functions) and access lots of in-built properties.

By binding the values to a prepared query, you prevent the injection. The 'ss' in the bound parameters is an abbreviation of the data types the query will expect, so in this:

$stmt->bind_param("ss", $email, $password);

You are telling your query that it will receive 2 strings (ss) which are passed from $email and $password. The other abbreviations available are i (integer), d (decimal), and b (blob), so if your values included an integer, a decimal and a string you'd have:

$myQty = 7;
$myName = "Chris";
$myCost = 12.48;

//bind an integer, a string and a decimal
$stmt->bind_param("isd", $myQty, $myName, $myCost);

As Gary said, by preparting a query, you do that once, and then bind and execute as many times as you need:

I've engaging a number of tutorials and online resources in an attempt to understand all this and right about the time I run into a question, I'll come back to this page and see that thing explained in greater detail.

Chris, I get the ss and the additional commentary you provided as far as decimal and integer is very much appreciated.

Going back to the theory behind all this: If I were to stay with my football analogy, my class is the playbook. The moment I instantiate that class, I'm calling a play and what was a "plan / class" now becomes an "action / object" in that I'm positioning my players on the field and getting them situated so they can actually advance the ball, correct?

I would tend to think of it as the class encompassing every aspect of a football team, and an object being a specific football team. The class includes properties such as Team Name, Stadium, Nickname, Players and methods such as GoForAttack the Goal (???). Now you create a specific team by instantiating the class:

Right - you've started to mix 2 libraries together: you're connecting to your Database using PDO, and then using the mySQLi methods to access your data! You need to choose 1 and stick to it...(we've been using mySQLi up until now).

While they are similar, they are not the same. Here's the parameter binding in the 2 libraries:

//mySQLi
$stmt->bind_param("s", $email); //bind the data to the query

//PDO
$stmt->bindParam(1, $email, PDO::PARAM_STR);

Aside from the connection library used, there's a couple of other issues with your code:

The IF statement is not in context of the Query statement - it's in context of the prepare() statement. You're checking to makes sure the query was 'prepared' correctly (i.e had no errors in it). If it was, then you can continue on and execute it...

Sorry, I left a debug line in from my test. I've edited the code above now, but you need to remove the following line from your code:

var_dump($info)

0

brucegustPHP DeveloperAuthor Commented: 2013-10-11

Chris and Gary, I went back to the mysqli approach in order to achieve the kind of consistency y'all recommended and also to try and eliminate some of the errors being done by virtue of my not being as familiar with a purely PDO approach.

That said, I was able to get my code to work, save one final element. Here's the working code as far as the total number of rows being recognized and advancing the user to the homepage as opposed to being redirected to the "wrong login" page:

Line 17 is written in kind of shorthand so it's a little trickier to understand.

Basically, after you've executed a query, the fetch() command tries to pull a record from the query results. Each time you call fetch() it gets the next record. When it can no longer fetch a record (either because there were none to begin with) or it's reached the last record, then the fetch() command returns FALSE.

Line 17 uses this and it's probably easier to understand if it's broken into 2 lines

$row = $stmt->fetch(PDO::FETCH_OBJ); //fetch the record into $row or set it to false if we have no recordsif ($row) { //if we don't have any records, $row would be false. If we do, $row has the database record