tag:blogger.com,1999:blog-3993498847203183398.post1224792376475154663..comments2024-03-29T11:00:39.953+00:00Comments on RevK<sup>®</sup>'s ramblings: INSERT INTO table SET field=value,...RevKhttp://www.blogger.com/profile/12369263214193333422noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-3993498847203183398.post-44746366779412769632016-03-11T08:08:41.838+00:002016-03-11T08:08:41.838+00:00Wow that was odd autocorrectWow that was odd autocorrectRevKhttps://www.blogger.com/profile/12369263214193333422noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-1120899708223072092016-03-11T08:08:15.079+00:002016-03-11T08:08:15.079+00:00Hang on, even the result is not valid UTF8 and nev...Hang on, even the result is not valid UTF8 and never could be. I will look in to that but I am not ℅ incredibly makes sense.RevKhttps://www.blogger.com/profile/12369263214193333422noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-341204087925326862016-03-11T08:07:21.695+00:002016-03-11T08:07:21.695+00:00that does not look like valid UTF8. Indeed UTF8 is...that does not look like valid UTF8. Indeed UTF8 is carefully designed so you cannot have a "normal" character as part of the sequence. In it an interesting one which I will check anyway.RevKhttps://www.blogger.com/profile/12369263214193333422noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-16154151839760156482016-03-10T21:43:16.259+00:002016-03-10T21:43:16.259+00:00Lots of libraries exist to generate SQL. That'...Lots of libraries exist to generate SQL. That's how ORMs are able to transparently provide an abstraction of a database upon object oriented function calls. jmzhttps://www.blogger.com/profile/06062695428471030499noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-24119294614485632342016-03-10T21:37:35.523+00:002016-03-10T21:37:35.523+00:00Escaping still isn't good enough, use the plac...Escaping still isn't good enough, use the placeholders in the way they were meant to be used. There are way too many corner cases for escaping. Here's an interesting one you may have not heard of - only works under MySQL because MySQL likes to do weird stuff, but the principle remains.<br /><br />Consider a parameter consisting of two bytes (one of which is a quote mark) passed to your escaping function. <br /><br />¿' (0xbf 0x27)<br /><br />The escaping routine takes this and returns three bytes:<br /><br />¿\' (0xbf 0x5c 0x27)<br /><br />This is actually interpreted as a multi-byte encoded sequence in MySQL! The result ends up being:<br /><br />縗' (0xbf5c 0x27)<br /><br />and you've successfully injected an unescaped quote mark. <br /><br />So yes, use SQL placeholders in every case. Never allow arbitrary user-supplied information to be used in generated SQL.jmzhttps://www.blogger.com/profile/06062695428471030499noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-71731408368174223312016-03-07T12:01:54.918+00:002016-03-07T12:01:54.918+00:00Insert and Update are such common SQL commands tha...Insert and Update are such common SQL commands that usually follow the same syntax each time (unlike a select that may involve a complicated join). It's a wonder that a library doesn't exist to build the SQL needed.Philhttps://www.blogger.com/profile/10021621329177687470noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-83119390118441694802016-03-05T15:22:48.007+00:002016-03-05T15:22:48.007+00:00We have tools where it is not simply %s, it is act...We have tools where it is not simply %s, it is actually properly escaped in query strings that are malloced not foxed size. We know Little Bobby Tables quite well and keep him locked up.RevKhttps://www.blogger.com/profile/12369263214193333422noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-66287989439433278752016-03-05T15:20:27.307+00:002016-03-05T15:20:27.307+00:00Putting %s in insert or update statements is bad p...Putting %s in insert or update statements is bad practice IMO. I'd always use bind variables because you never know what's going to end up there..<br /><br />Not sure you save much anyway. Once you've written the code to generate the update/insert - which normally happens once - you're passing it arrays of fields and things anyway. Unless you're hand coding the queries, which has its place of course.. OTOH in that case you save nothing as you're writing it each time anyway.<br />Tony Hoylehttps://www.blogger.com/profile/06485210895681350152noreply@blogger.comtag:blogger.com,1999:blog-3993498847203183398.post-70086140690902811922016-03-05T11:15:30.364+00:002016-03-05T11:15:30.364+00:00It's rather unusual to hear someone talk about...It's rather unusual to hear someone talk about writing SQL code for simple CRUD. You'd be much better off using an ORM or API above the cursor level. Not only would your code be more portable to different backends but you'd benefit from reduced maintainance work when upgrading db versions.Matthew Wilkeshttps://www.blogger.com/profile/01510708328893867970noreply@blogger.com