397
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
This way, the dates can easily be sorted as strings using the default sorting rules (i.e. lexicographical sorting).
This is also why both month and day are specified using two digits (adding a ...
- 136k
254
votes
Accepted
Is it ever okay to use lists in a relational database?
The key word and key concept you need to investigate is database normalization.
What you would do, is rather than adding info about the assignments to the person or tasks tables, is you add a new ...
- 27.7k
170
votes
Accepted
Why not return dates as a string from the database?
Dates, DateTimes and really any other typed object, should generally be left in their properly typed format until the moment you need them to be made into some other type - especially when that type ...
- 6,132
154
votes
Accepted
Is it good practice to always have an autoincrement integer primary key?
It's never a bad idea to have a guaranteed unique row identifier. I guess I shouldn't say never – but let's go with the overwhelming majority of the time it's a good idea.
Theoretical ...
- 39.4k
148
votes
Accepted
Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?
The problem is that #1 requires you effectively parse and interpret the entirety of the SQL variant you're working against so you know if it is doing something it shouldn't. And keep that code up to ...
- 110k
137
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
Not mentioned yet, but you quickly gloss over the order inside YYYY. That's already millennia, centuries, decades, years. That is to say, YYYY is already ordered from longest period to shortest period....
- 8,820
133
votes
Accepted
Are database unique indexes a mask on bad scripting?
My suggestion was to make a UNIQUE index on whatever it is that defines the uniqueness of the table
You really ought to have one already.
Uniqueness of rows is a pretty fundamental property of a ...
- 13k
117
votes
Is it considered an anti pattern to write SQL in the source code?
You excluded the crucial part for simplicity. The repository is the abstraction layer for persistence. We separate out persistence into its own layer so that we can change the persistence technology ...
- 4,628
116
votes
Is it good practice to always have an autoincrement integer primary key?
TL;DR: Use UUID's instead of auto-increment, if you don't already have a unique way of identifying each row.
I disagree with all the answers before. There are many reasons why it is a bad idea to add ...
- 2,863
79
votes
Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?
Because option 1 is not a solution. Screening and filtering means rejecting or removing invalid input. But any input might be valid. For example apostrophe is a valid character in the name "O'Malley". ...
- 60k
73
votes
How can I protect an SQL connection string in a client-side application?
You don't.
You put your SQL Server behind some sort of (probably web) API and use any of the numerous methods for securing an API. Anything which exists on a client's machine is not secure.
- 25k
69
votes
Are database unique indexes a mask on bad scripting?
UNIQUE constraints are supported by all relational databases for good reasons, one of them being able to prevent data inconsistencies.
Your colleague has a point in that the code should be written to ...
- 17.1k
68
votes
Accepted
Can it be acceptable to construct SQL queries dynamically?
On one hand, I don't like such braindead rules, since they are clearly an oversimplification. A better wording would be IMHO
You shouldn't construct SQL queries dynamically using unsanitized input ...
- 212k
65
votes
Is it good practice to always have an autoincrement integer primary key?
Autoincemental keys have mostly advantages.
But some possible drawbacks could be:
If you have a business key, you have to add a unique index on that column(s) too in order to enforce business rules.
...
- 39.4k
60
votes
Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?
If you're trying to do string processing, then you're not really generating an SQL query. You're generating a string that can produce an SQL query. There's a level of indirection that opens up a lot ...
- 1,630
59
votes
Accepted
SQL sanitizing in code with no user input
If there is never any user input at all, or the program is only used internally, the importance of sanitizing should be reduced.
But there are still a few possible reasons to always do so
If all ...
- 5,880
58
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
Is there any reason at all?
Yes. Those pieces of software will be using ISO 8601.
ISO 8601 has a number of advantages over other date formats:
It's a standard with a spec document :)
It's ...
- 734
56
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
It's because all the other ways to do it are ambiguous.
01/02/2003 what does that mean? January second 2003? Or in Europe: February 1st 2003? It gets even worse if you use two digits for the year, ...
- 13.3k
56
votes
Accepted
Is it okay to hard-code table and column names in queries?
am I being paranoid about futureproofing?
tl;dr: My view is: "yes."
having configurable table ... names in your queries
No, that typically is not helpful.
I have worked with lots of ...
- 7,620
55
votes
Is it considered an anti pattern to write SQL in the source code?
Most standard business applications today use different layers with different responsibilities. However, which layers you use for your application, and which layer has which responsibility is up to ...
- 212k
54
votes
Why do beginners still write SQL? Why do we not push them to use a higher level abstraction?
Simple.
Because there is no way you would really understand how to use a database if you're only interacting with it through the ORM.
This already happened in practice to many beginner programmers ...
- 136k
54
votes
Accepted
Are surrogate keys a known anti-pattern?
The use of surrogate keys is by itself not an anti pattern. It is a way to create a stable primary key for an entity, that will never change and not depend on any application data.
But there are three ...
- 79.9k
53
votes
Why not return dates as a string from the database?
He is saying to use the web server to convert the data time to a string. I am saying do it on the database server and not the web server. Why do you think that is better? - M T Head
I want to know ...
- 112k
43
votes
Accepted
Is domain driven design an anti-SQL pattern?
These days, you are likely to see reads (queries) handled differently than writes (commands). In a system with a complicated query, the query itself is unlikely to pass through the domain model (...
- 33.4k
41
votes
SQL sanitizing in code with no user input
Parameterised queries should be your standard approach to all SQL. If you are trying to find reasons why you don't have to use them, then you are doing yourself a disservice.
In your example you have ...
- 78.6k
37
votes
Is it ever okay to use lists in a relational database?
You're asking two questions here.
First, you ask if its ok to store lists serialized in a column. Yes, its fine. If your project calls for it. An example might be product ingredients for a catalog ...
- 39.4k
36
votes
Is it considered an anti pattern to write SQL in the source code?
Marstato gives a good answer but I'd like to add some more commentary.
SQL in source is NOT an anti-pattern but it can cause problems. I can remember when you used to have to put SQL queries into the ...
- 6,152
36
votes
Accepted
Why should I use foreign keys in database?
It maintains referential integrity (yes but can be maintained without it too)
You are technically correct that if you're able to maintain referential integrity yourself, you don't need the constraint ...
- 54.9k
Only top scored, non community-wiki answers of a minimum length are eligible