Inserting values using PDO

Question

•So I'm trying to change my coding habits and want to at least prevent SQL Injections. However, I'm still confuse about the parameters or syntax in creating a query. For instance,

$q = //LINE 1 "insert into tblProject(projectName, projectLocation, projectType, projectStatus) //LINE 2 values(:projectName, :projectLocation, :projectType, :projectStatus);";

I believe that the first line refers to the column name in the database , however in LINE 2, what does ':" means and what does it do? Where does the values inside the values() references? Does it refers to the variable I declared, for instance,$projectName = $_POST['projectName'];. Does it refer to the $projectName or the value inside the $_POST['projectName']?

•Another question is all about this ...->execute(array(...)); Let's use this code as example:

$results = $query->execute(array(
":projectName"      => $projectName,
":projectLocation"  => $projectLocation,
":projectType"      => $projectType,
":projectStatus"    => $projectStatus
));

Can you explain briefly but precise what it does? And also, where does :projectName and so on.. Came from or where is their origin?

•It uses an array(). Therefore, if I were to only update or insert a single value and use execute(array()), will it cause me any error?

I believe I ask too much question, any good references where I can find most of the answers here?

Thanks in advance.

With PDO you can use both named `:someName` and unnamed `?` placeholders. These will simply be substituted with the values you pass into execute. You can read more in the documentation: http://php.net/manual/en/pdo.prepare.php — JimL, Mar 23 '16 at 18:25

score 2 · Accepted Answer · answered Mar 23 '16 at 18:27

2

The two parts of your question are the same. The names with the colons is how you specify the name of your binding. When you use the bindValue/bindParam or execute, you say "this :parameter is actually this value". So it will take your query:

$q =  "insert into tblProject(projectName, projectLocation, projectType, projectStatus) 
   values(:projectName, :projectLocation, :projectType, :projectStatus);";

Then when you execute it with this:

$results = $query->execute(array(
":projectName"      => $projectName,
":projectLocation"  => $projectLocation,
":projectType"      => $projectType,
":projectStatus"    => $projectStatus
));

the driver will go through and say "Okay, so the value of $projectName needs to be escaped and used in place of :projectName in the query, and $projectLocation should be :projectLocation..." and so on and so forth

answered Mar 23 '16 at 18:27

aynber

22,380
8
50
63

Oh. Where did the names with colons came from? Is it the name of the `` or somewhere else? – juju17 Mar 23 '16 at 18:30
@user4932301 you can name them whatever you want. The point is that with named placeholders the name you have in the query will be matched to the names passed into execute. With unnamed placeholders they will be matched by order. – JimL Mar 23 '16 at 18:33
@user4932301 you just type them. You can call them what ever you want (within reason http://stackoverflow.com/a/5810058/4333555), e.g. `:dumbplaceholder1` then bind `execute(array( ":dumbplaceholder1" =>` – chris85 Mar 23 '16 at 18:33
1

OHH I'm getting it now. So, the value of the placeholders is given on the `execute(array())`? – juju17 Mar 23 '16 at 18:35
1

@Jerlon Yup, that is correct, or it could be done in a `bind` function call. Take a look at http://php.net/manual/en/pdo.prepared-statements.php. – chris85 Mar 23 '16 at 18:53

Inserting values using PDO

1 Answers1