5 ms·
Yes, yes, yes a million times yes. Every person starting PHP should be taught PDO with parameterized queries from the get go. The internet (and many respectab
by pak 15y ago
Yes, yes, yes a million times yes. Every person starting PHP should be taught PDO with parameterized queries from the get go. The internet (and many respectable CS courses) are littered with examples using mysql_query() and string concatenation, half of which train people to create SQLI bugs or don't explain why escaping is needed; it's too late to undo the damage already done but at least people new to PHP will hopefully be told now that there is a better way.
If we had trained people to simply do
$sth = $dbh->("INSERT INTO folks (name, addr, city) values (?, ?, ?);");
$sth->execute(array($name, $addr, $city));
in 2005, when PDO first came out, uncountable SQLI bugs would have been avoided, and maybe lulzsec wouldn't be having such a field day in 2011...
- eropple 15y agoEven 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.
- deleted 15y ago[deleted]
- preek 15y agoTwo things sometimes irk me: How much code there is to write for simple DB interaction + coupling of two different languages (SQL + [programming lang]). In Rails I'd write: Folk.create(:name => some_name, :addr => some_addr, :city => some_city) One line, easily readable and only one language (Ruby). This is not meant to be a flame post - I'm just asking: Is it the preferred way in PHP to prepare SQL statements yourself?
- duskwuff 15y agoActiveRecord is an abstraction. Which is good in general, but when you want to get your hands dirty with some SQL it's a pretty serious hindrance.
- wavesplash 15y agoActiveRecord lets you write raw sql any time you need to. AR's design goal was to just reduce the drudgery, not prevent you from using the full power of sql. No 'serious hindrance' involved.
- 1880 15y agoWell, if you use a PHP framework or library, of course there are easier and less verbose ways to prepare SQL statements. I don't think RoR vs PHP would be an apples to apples comparison.
- smackfu 15y agoIt's only "one language" because the Rails framework is hiding the SQL away from you. You can bolt a framework on top of PHP too.
- mh- 15y agothis reply isn't meant to be a flame, but- Do you not understand why you can't compare Rails (framework) to PHP (language)? PHP has frameworks with ORM, too.
- pavel_lishin 15y ago> The internet (and many respectable CS courses) are littered with examples using mysql_query() and string concatenation Not to mention, "PHP: The Good Parts". I almost threw that book out when I hit those parts.
- jdunck 15y agohttp://www.amazon.com/PHP-Good-Parts-Delivering-Best/product-reviews/0596804377/ http://www.amazon.com/PHP-Good-Parts-Delivering-Best/product... ...Probably the right move.