Page 1 of 1
How do you write a simple PDO query?
Posted: Tue Jun 13, 2017 5:40 am
by simonmlewis
Code: Select all
$query = $pdo->query("DELETE FROM homepage WHERE id = '$id'");
Just been updating a ton of code to be PDO, and thought there was this shorter way of doing it, if only Admin are running it so doesn't need the injection protection.
It doesn't work.
Is it because query is for queries, and execute is for UPDATE/INSERT ?
What is the correct format for a UPDATE/INSERT simple query?
EDIT: I think it is because my older Mysql query escaped the apostrophes.
This is correct as far as I can tell, but the update won't run, because of "simon's" type text.
Is there a means to make it work? I'm sure I am missing something silly.
Code: Select all
$query = $pdo->exec("UPDATE homepage SET content = '$hometoptext' WHERE id = '$id'");
Re: How do you write a simple PDO query?
Posted: Tue Jun 13, 2017 7:45 am
by requinix
A
shorter way? Not really. Not sure where there's room for shortening...?
simonmlewis wrote:
if only Admin are running it so doesn't need the injection protection.
No. No no no. Stop thinking that. It doesn't matter whether you believe you can trust your user or not, that line of thought is wrong and it is
always wrong.
If you don't want to believe that your admin is untrustworthy, consider that maybe they're not as technically literate as you and they might do something bad accidentally. Or maybe consider that your application isn't as safe as you think it is and maybe some malicious user will trick your good users into doing something they shouldn't.
simonmlewis wrote:Is it because query is for queries, and execute is for UPDATE/INSERT ?
query() lets you get access to resultsets from a query, execute() does not. However both types of query will work with both methods.
If you're having problem with code that doesn't work then you have to post
that code.
simonmlewis wrote:What is the correct format for a UPDATE/INSERT simple query?
I don't know what you think a "simple query" is.
simonmlewis wrote:I think it is because my older Mysql query escaped the apostrophes.
That would be an older PHP, not an older MySQL. And yes, that did happen in the past, but they got rid of it because it was bad.
simonmlewis wrote:This is correct as far as I can tell, but the update won't run, because of "simon's" type text.
Is there a means to make it work? I'm sure I am missing something silly.
Yes, you're missing something.
Re: How do you write a simple PDO query?
Posted: Tue Jun 13, 2017 7:51 am
by simonmlewis
Ok. So the rule here is, no matter where an UPDATE/INSERT is added, always use the :variable method, to keep it safe and secure.
When I updated the code to use that, it worked. It's because of those ', that is failed. As soon as I used the :method, it worked.
I have added several simple one-line queries, but I think they ought to all be :method so that it's safe.
I see your point about always using it. Not just to avoid hacks, but in case someone puts something 'stupid' in the box by mistake. Or even if I did a silly 'Paste', and update instantly.... which pasted code in, rather than text.
Re: How do you write a simple PDO query?
Posted: Tue Jun 13, 2017 11:03 am
by simonmlewis
This will not work. It won't insert. It won't show errors on screen (ie, too many in the array for those in the query), and if I ask it to show the last ID added, it just shows a zero. Nothing added.
Code: Select all
$query = ("INSERT INTO homepage (url, image, section, freetext, priority, content, homepromocolor, homepromotextcolor, stockbanner, dateadded) VALUES (:searchurl, :saved_filename, :section, :freetext, :priority, :content, :homepromocolor, :homepromotextcolor, :stockbanner, :todaydate)");
$result = $pdo->prepare($query);
$result->execute(array(':searchurl' => $searchurl, ':saved_filename' => $saved_filename, ':section' => $section, ':freetext' => $freetext, ':priority' => $priority, ':content' => $content, ':homepromocolor' => $homepromocolor, ':homepromotextcolor' => $homepromotextcolor, ':stockbanner' => $stockbanner, ':todaydate' => $todaydate));
If I revert back to this:
Code: Select all
mysql_query("INSERT INTO homepage (url, image, section, freetext, priority, content, homepromocolor, homepromotextcolor, stockbanner) VALUES ('$searchurl', '$saved_filename', '$section', '$freetext', '$priority', '$content', '$homepromocolor', '$homepromotextcolor', '$stockbanner')");
It works.
So what is missing or wrong in that PDO query?
Re: How do you write a simple PDO query?
Posted: Tue Jun 13, 2017 11:27 am
by Christopher
Read the manual for the difference between PDO::exec() and PDOStatement::execute(). They are totally different. Both require that you to prepare a statement before using. Neither return results.
PDO::exec()
http://php.net/manual/en/pdo.exec.php
PDOStatement::execute()
http://php.net/manual/en/pdostatement.execute.php
Re: How do you write a simple PDO query?
Posted: Tue Jun 13, 2017 11:30 am
by simonmlewis
What have I done wrong then?
Re: How do you write a simple PDO query?
Posted: Wed Jun 14, 2017 12:49 pm
by thinsoldier
http://php.net/manual/en/pdo.query.php
If you haven't actually done a PDO tutorial before diving into updating your project code, stop. Go do some tutorials.
Re: How do you write a simple PDO query?
Posted: Wed Jun 14, 2017 12:55 pm
by simonmlewis
I have built an entire site using PDO. But need to convert this code, and it's not working.
Re: How do you write a simple PDO query?
Posted: Wed Jun 14, 2017 2:36 pm
by requinix
Could it have anything to do with the dateadded you introduced?
Fact is, if the statement executes then either (a) a row will be inserted or (b) there will be an error or warning indicating why it did not insert. One of those is definitely happening.