|
I agree, but wouldn't you still land the incoming data in a staging table before performing such checks and moving the data to its destination?
|
|
|
|
|
Most likely yes. And if tokens needs to be extracted I would try to do it in the middle tier before streaming the data into temp tables. But I can't rule out there are use-cases where that is just too slow - and if you are locked into a relational database this might be a solution in that case... my solution would probably be to change job 
|
|
|
|
|
Have you ever worked in an environment where your servers are not allowed to do direct table access?
If so, how do you update multiple records from a stored procedure? Or even do a request for multiple results using multiple keys from a stored proc *while maintaining atomic operations?*
I've run into this situation several times, where at the very least I needed to send a delimited list of ints to a stored proc, and parse it in the proc. That can be done with splits of course, but to do it cleanly you actually need to do more than that, to check for multiple runs of whitespace and such (or commas if you're doing that)
What then? Have you ever run into that?
Real programmers use butterflies
|
|
|
|
|
Nope. Stored procedures for security was the norm back when VB applications accessed the database directly. Modern .NET or Java makes it a lot easier to write secure code than stored procedures - and if you want security you want the code to be as simple as possible.
I have run into people who questioned this (and typically their security knowledge was... well... not as up to date as one could wish), but I have never had any serious issue deploying into pretty secure environments. I am sure those environment exists as all it takes is a CTO stuck in the past. I would avoid a work environment where this is the case - and the product I work on now will not be sold to a customer that have this requirement.
|
|
|
|
|
That's good to know. It has been years since I coded for SQL professionally. I didn't realize that had changed so much.
Real programmers use butterflies
|
|
|
|
|
One thing I forgot to mention - if the rule is "no machine with direct internet access is allowed to connect to the database", then standard reverse proxy between the internet exposed endpoint and the "real" webserver will do the trick.
|
|
|
|
|
No, I've only worked for worthwhile employers.
|
|
|
|
|
Yes. The standard framework I developed and use for pretty much all my web-based apps is access via SPs only; no direct table access at all. Generally I don't need to update multiple rows but I certainly can do, though I prefer to avoid it. If there are multiple updates required then I wrap the lot in a transaction persisted across SP calls. (For one thing, if something fails the webserver then has a much easier time of working out what failed).
And yes, there are also occasions when I need to send delimited lists to an SP (typically keys to match an IN clause) but then I just use a UDF to parse it and return a table result. (And the webserver is responsible for ensuring the list is "clean" wrt whitespace etc).
Conceptually, I'm not sure I'd feel comfortable loading an unverified CSV straight into the database using generic type validation routines. Generally there's more to checking input than just checking field type, and that sort of stuff belongs in the business layer, well away from the database. It feels a bit like this is a workaround to gain direct table access by the backdoor, bypassing the strong typing that SP parameters at least partially enforce.
|
|
|
|
|
I don't disagree with what you wrote, I'd just like to clarify that "typed" is my term.
What's literally happening is each field is being regex checked for consistency, such that Integers are matched with one, strings with the other. The text is "tokenized"
Real programmers use butterflies
|
|
|
|
|
A big thank you to all those who have served or are serving.
I see even ![Green Alien | [Alien]](https://www.codeproject.com/script/Forums/Images/alien.gif) has a red poppy.
"the debugger doesn't tell me anything because this code compiles just fine" - random QA comment
"Facebook is where you tell lies to your friends. Twitter is where you tell the truth to strangers." - chriselst
"I don't drink any more... then again, I don't drink any less." - Mike Mullikins uncle
|
|
|
|
|
For a second I thought got shot, but then I have no evidence he would even bleed red.
|
|
|
|
|
Shirley would simply curve the space around him, bullets shmullets.
"the debugger doesn't tell me anything because this code compiles just fine" - random QA comment
"Facebook is where you tell lies to your friends. Twitter is where you tell the truth to strangers." - chriselst
"I don't drink any more... then again, I don't drink any less." - Mike Mullikins uncle
|
|
|
|
|
It's actually Armistice Day over here.
|
|
|
|
|
Indeed it is.
"the debugger doesn't tell me anything because this code compiles just fine" - random QA comment
"Facebook is where you tell lies to your friends. Twitter is where you tell the truth to strangers." - chriselst
"I don't drink any more... then again, I don't drink any less." - Mike Mullikins uncle
|
|
|
|
|
And Remembrance Day in Canada, and surprised that an American didn't ask why ![Green Alien | [Alien]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/alien.gif) was wearing a flower!
|
|
|
|
|
Not knowing that it is Remembrance Day in Canada, and recalling that does US-centric themes from time to time, like 4th of July. That's why I noted it, but didn't ask.
"the debugger doesn't tell me anything because this code compiles just fine" - random QA comment
"Facebook is where you tell lies to your friends. Twitter is where you tell the truth to strangers." - chriselst
"I don't drink any more... then again, I don't drink any less." - Mike Mullikins uncle
modified 11-Nov-21 15:50pm.
|
|
|
|
|
As a U.S.Navy VietNam veteran, I appreciate the sentiment.
Actually, it was probably the best 4 years of my life.
Anchors Aweigh!
|
|
|
|
|
...so nobody listening to me...
"The only place where Success comes before Work is in the dictionary." Vidal Sassoon, 1928 - 2012
|
|
|
|
|
Did somebody say something? 
|
|
|
|
|
Patient: Doctor doctor people keep ignoring me
Doctor: Next
"Life should not be a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming “Wow! What a Ride!" - Hunter S Thompson - RIP
|
|
|
|
|
I am right here with you. People hear the sounds but don't try to get the meaning.
oeifSMIORJRJ!
|
|
|
|
|
.. and crotchety and lots of other things but there some things I'm not sure that I want to understand. I'm no longer brave enough to ask "Why?"
There was a conversation in QA yesterday (Programming, data storage, database[^]) about storing numeric IDs as strings. Drives me to distraction and I'm obviously not alone
Today I find a column at work called ACTV_IND. Hm, thinks I, could this column contain an indicator to state whether or not this item is currently "Active".
Well, yes it does.
Except instead of an "indicator" it contains the words "Yes" or "No".
I was sort of expecting a char Y or N, or even a bit 1 or 0. But it's "Yes" or "No" .This in a database that has otherwise been normalized to death (oh yeah, there are max 5 "type" of record and each type is max 3 chars - but that has been normalized away into a separate table). At a site where they are talking about destroying historical data "to save storage space".
Just when I thought it couldn't get any worse, I noticed the table schema has this as varchar(15). That's longer than the longest word I've ever heard of for "Yes"! (Urdu by the way - Dschii haan, except it wouldn't be in that alphabet, but there again varchar not nvarchar)
I don't know what made me do it, perhaps some innate sadomasochistic streak, but I checked out the numeric Ids while I was there ... yep, they're all varchar(15) as well.
I'm just going to quietly write my query, get the data and get outta here.
|
|
|
|
|
Ouch. I was looking for the appropriate emoji thing to respond to this post with, but nothing quite fits.
It does remind me of a time an otherwise brilliant (and self taught) programmer was doing all his JOINs in PHP instead of in SQL.
I got out of there, so I understand. There are simply wrong ways to use a database, although recently I *might* be guilty of that myself (as per my post at the top of the lounge at the moment) *hides*
Real programmers use butterflies
|
|
|
|
|
If you do not use a string as datatype for this column, how would you fit "FILE NOT FOUND" into it? 
|
|
|
|
|
and, if you don't define it as a string, how on earth do you get key values like "one", "two", "three" etc. into it??
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
