Named Query Advice: Don't name your parameters the same as field names

I discovered the use of a bad naming convention yesterday. I had a db table with a field named cut_id. I subsequently had a named query that did an update based on a parameter that I also named cut_id, so the where clause was

where cut_id = :cut_id

The problem with naming the parameter the same thing as the field name is that I made a typing mistake and forgot the : in front of the parameter.

Instead of throwing an error, the query was perfectly valid since cut_id was a field name. It basically would run the query as if you had written it as

where 1 = 1

Lessen learned the hard way once again :slight_smile:

4 Likes

Ooof. How’d you find out? I usually do exactly what you do with the parameter name matching the column name as it made sense to me. How do you do it now?

I was watching one of my dashboards in development and suddenly I saw the unprocessed statistic go from about 1200 to over 15,000. My heart sank when I saw it happen, but luckily from a previous mishap, I have several backups on hand to restore.

Now I would do it with similar naming conventions but with a slight adjustment that let me know that this is a passed parameter. Something like, always starting the parameter with an underscore.

1 Like

Yea I was going to say an maybe start the parameters with a p_ or something.

I know the feeling. I once ran an DELETE query in MySQL and with a WHERE id -= 13456 instead of WHERE id = 13456 and it deleted every single record EXCEPT the one I wanted. Luckily had a backup but still lost a few hours of data. Taught me the hard way to never run an Update or Delete query without running it as a Select query first.

3 Likes

Ouch…:dizzy_face: Good thing to keep in mind! Thanks

1 Like

Funny thing is I had tested it, and it was working for weeks. At some point while in that query I must have accidently deleted the colon some how.

If anyone else ever touched the project at any point, it’s their fault it happened :wink:

1 Like