5 ms·
Even that way is kinda fragile and fugly, though. I always recommend: $stmt = $somePDOObject->prepare("INSERT INTO folks " . "(name, addr, city) values (
by eropple 15y ago
Even that way is kinda fragile and fugly, though. I always recommend:
$stmt = $somePDOObject->prepare("INSERT INTO folks " .
"(name, addr, city) values (:name, :addr, :city);");
$stmt->bindValue(":name", $name);
$stmt->bindValue(":addr", $addr);
$stmt->bindValue(":city", $city);
Slightly more verbose, but it has the upside of being more resilient.
- Groxx 15y agoIf you go the resilient route with everything you end up with code that looks like Java code. It's not usually worth it, as when the query changes, odds are you'll be looking at the parameters, and will notice changes. Not true for large queries, of course, but for beginners? They're writing things like the above, where the entire process fits in a single eyefull. Making it more verbose from the get-go means they're more likely to just concatenate strings.
- masklinn 15y ago> If you go the resilient route with everything you end up with code that looks like Java code. Meh. Prepared statements are useful when you'll use the it multiple times through rebinding, and for one-shots it's easy enough to have a function taking a parameterized query and an array and doing the annoying crap internally. Then you just write: $result = executeSQL($somePDOObject, "INSERT INTO folks (name, addr, city) values (:name, :addr, :city);", array("name" => $name, "addr" => $addr, "city" => $city);
- eropple 15y agoThis is actually fairly close to what I do in my own code (I actually extend the PDO object, but same idea). The example I gave was just using the base PDO library instead.
- deleted 15y ago[deleted]
- 1880 15y agoWhat would be the difference, apart of the named parameters? I mean, does this add any security?
- masklinn 15y ago> I mean, does this add any security? No, or he would have mentioned it. It's clearer (especially as the number of parameters increase) and easier to change (in order to e.g. add a new parameter)
- palish 15y agoIt's not more clear when you're trying to examine the codebase as a whole.
- eropple 15y agoOf course it is. Names are clarity. Position-based binding sucks. I wrote a quick extension to java.sql.PreparedStatement at work one evening this week to enable named parameters specifically because of this. Named parameters also make it easier to re-use queries (have a queries file or something) because you're much less apt to immediately break something by changing a query if everything is explicitly bound via names. And you can make the statements as a whole more terse if you'd like; I intentionally wrote it verbosely.
- encoderer 15y agoIn the first example above, you have one place where you have position based binding that you could break while reusing or refactoring. In your example, you have one place where you have position based binding that you could break while reusing or refactoring.
- eropple 15y agoIn my experience, people are vastly less likely to rename a variable than move it to a different place in a query. There's also the proclivity for people to want to reuse the same value in multiple places in a query. Name-based binding makes that a lot more readable and straightforward.
- 9ec4c12949a4f3 15y agoI've designed a simple way to deal with this where I pass a string (the SQL) and the params as a 2-d array, it ends up looking basically the same as the above. It lets me feel like I'm passing json, which makes me happier.
- etherealG 15y agoresilient to what?
- pavel_lishin 15y agoThe ? ? ? example is readable when you're inserting three values; it becomes really really easy to leave off, or add on an extra, argument when you're inserting fifteen or fifty values. Think fast: is everything in the code below correct? I've already forgotten whether I've added an extra ? or left one off, and I just typed this 30 seconds ago. $sth = $dbh->("INSERT INTO a_table (lorem, ipsum, dolor, sit, amet, consectetur, adipiscing, elit, donec, malesuada, purus, et, tellus, dignissim, tristique, in, ipsum, neque, ultricies, quis, hendrerit, ac, pulvinar, eget, enim) ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"); $sth->execute(array($lorem, $ipsum, $dolor, $sit, $amet, $consectetur, $adipiscing, $elit, $donec, $malesuada, $purus, $et, $tellus, $dignissim, $tristique, $in, $ipsum, $neque, $ultricies, $quis, $hendrerit, $ac, $pulvinar, $enim, $eget));
- aquadoctorbob 15y agoTrick question; you've specified the `ipsum` column twice anyway. ;)
- etherealG 15y agoI think it's a good idea to be abstracting this away anyway. you should have some light ORM that hides actual sql statements away, that way you don't run into this problem. also, the named version above can have query length issues I think.
- pak 15y agoPerhaps; to avoid any verbosity for any real code I wrap PDO in something like IdiORM and Paris (http://j4mie.github.com/idiormandparis/ http://j4mie.github.com/idiormandparis/) or my fork called Dakota (https://github.com/powerpak/dakota https://github.com/powerpak/dakota). My point was that you can write a PHP + MySQL toy example with PDO that is just as simple as the mysql_query() garbage but is a million times safer in the long run.
- eropple 15y agoOh, totally, don't get me wrong. For a one-off query I might write what you did and think nothing of it. It's vastly better than the alternative.
- troels 15y agoI prefer: $stmt = $somePDOObject->prepare("INSERT INTO folks " . "(name, addr, city) values (:name, :addr, :city);"); $stmt->execute( array( ":name" => $name, ":addr" => $addr, ":city" => $city)); since it treats the statement as a function call, rather than some chunk of state that you manipulate.
- eropple 15y agoTotally forgot you could do that. Way cool.