Insert into database using PDO and OOP

[Unknown98]

I've begun to play around with object oriented php and I was wondering how I would go about writing a method that inserts values into a table. 

 

I have a class, called queries, and I made this very simple function:

class queries {

    public function insert($table, $field, $value){

    global $dbh;
 
    $stmt = $dbh->prepare("INSERT INTO $table ($field) VALUES (:value)");
    $stmt->bindParam(":value", $value);
    $stmt->execute();
    }

}

$queries = new queries();

Then I call it like this:

$queries->insert('members', 'username', $_POST['username']);

Which all works fine, but obviously it only inserts the one value, their username.

 

 

From searching around on the internet I think what I'd have to do is put the fields and values into an associative array, and then somehow loop through that array in my function. Does anyone know of any good tutorials on how to do this? 

[Jacques1]

You should generally avoid messing with home-made query generators unless you're very experienced and fully understand the implications. Otherwise, you'll quickly end up with tons of SQL injection vulnerabilities without even realizing it.

 

For example, in your code above, both $table and $field are entirely unprotected. You drop them right into the query string. So if you ever let the user provide the table or column name (like in a dynamic search), you immediately have a security vulnerability. And you cannot even see it from the outside. That is obviously a problem.

 

In general, this problem has been solved many times by very smart people, so you're better off using their results rather than reinventing the wheel. For example, all big frameworks like Laravel or Symfony have a query builder, and some of those are available as a standalone package:

• The Doctrine Project from Symfony
• The Illuminate Database component from Laravel

Of course you're free to build your own classes on top of those. The point is that they provide a solid foundation and help you avoid major mistakes (like using unescaped identifiers).

[ginerjm]

You really don't want to spend time making this one-at-a-time query.  Very resource intensive which you don't want to do.  I appreciate that you want to have a class, but why not write the query outside of the class and pass the string var containing it?  For the arguments you have it right - create an array and pass it to the function doing the execute or the prepare.

[Unknown98]

You should generally avoid messing with home-made query generators unless you're very experienced and fully understand the implications. Otherwise, you'll quickly end up with tons of SQL injection vulnerabilities without even realizing it.

 

For example, in your code above, both $table and $field are entirely unprotected. You drop them right into the query string. So if you ever let the user provide the table or column name (like in a dynamic search), you immediately have a security vulnerability. And you cannot even see it from the outside. That is obviously a problem.

 

In general, this problem has been solved many times by very smart people, so you're better off using their results rather than reinventing the wheel. For example, all big frameworks like Laravel or Symfony have a query builder, and some of those are available as a standalone package:

• The Doctrine Project from Symfony
• The Illuminate Database component from Laravel

Of course you're free to build your own classes on top of those. The point is that they provide a solid foundation and help you avoid major mistakes (like using unescaped identifiers).

 

Hmm.. I see what you mean. To be honest I haven't heard of either of those and didn't even know things like that were out there. I'll definitely look into them. 

 

You really don't want to spend time making this one-at-a-time query.  Very resource intensive which you don't want to do.  I appreciate that you want to have a class, but why not write the query outside of the class and pass the string var containing it?  For the arguments you have it right - create an array and pass it to the function doing the execute or the prepare.

 

Do you mean create the insert query (or whatever it happens to be) outside of the class, and then inside the class do something like this:

 

$stmt = $dbh->prepare($sql);

 

Is it better practice to do it that way?

[Supervan]

Hmm.. I see what you mean. To be honest I haven't heard of either of those and didn't even know things like that were out there. I'll definitely look into them. 

 

 

Do you mean create the insert query (or whatever it happens to be) outside of the class, and then inside the class do something like this:

 

$stmt = $dbh->prepare($sql);

 

Is it better practice to do it that way?

look at this online training, regarding oop

 

http://www.youtube.com/playlist?list=PLfdtiltiRHWF5Rhuk7k4UAU1_yLAZzhWc

[Unknown98]

Wow, awesome in depth tutorial. Thank you!!