February 6, 2012 at 6:24 am
You can encript yours SPs.
But I never used it in real world, I dont know if its easy to break.
From BOL:
"
...
ENCRYPTION
Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.
This option is not valid for CLR stored procedures.
Procedures created by using this option cannot be published as part of SQL Server replication.
...
"
February 6, 2012 at 6:40 am
naw, it's pretty easy to review all the procs, even when they are encrypted...it's really just "obfuscation", so it just requires a couple of extra steps to see the code when it's encrypted;
here's a recent post on the how to do it i did recently:
http://www.sqlservercentral.com/Forums/FindPost1238653.aspx
Lowell
February 6, 2012 at 6:44 am
cengland0 (2/6/2012)
jcb (2/6/2012)
SQL Injection and Performance are 2 items from a wide list why I hate dynamic sql.Some app use, at least, parameterized dynamic sql and it blocks most problems with SQL inject.
A example for C#:
conn.Open();
cmd.CommandText = @"UPDATE mytable SET
mycolumn = @myvariable
WHERE myid = @myid";
cmd.Parameters.AddWithValue("@myid", myobject.myid);
cmd.Parameters.AddWithValue("@mvariable", myobject.myproperty);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Yes, that's exactly how I pass my parameters. It takes care of the SQL injection problems automatically. I'm not aware of any current methods for injecting SQL code by using this method. If anyone else is, please show me how and I might change the way I create applications in the future.
Another benefit of putting your code into the .Net application instead of the stored procedures is that you can obfuscate the code so it cannot be stolen as easily. Imagine selling an expensive enterprise application and then having it easily copied because all your SQL code is embedded in easy to read stored procedures.
Running the SQL code inline, instead of in procs, doesn't actually help with that. Well, unless the people stealing it have never heard of Profiler or traces, of course.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2012 at 7:18 am
GSquared (2/6/2012)
Running the SQL code inline, instead of in procs, doesn't actually help with that. Well, unless the people stealing it have never heard of Profiler or traces, of course.
True but it does make it more difficult and you need have elevated permissions to do that on the SQL Server. Also, this will only give you queries that were used during the start and end of your trace. It may not have caught all possible report queries if those reports were not run during that time.
I like to make it as difficult as possible knowing that it's impossible to hide all the queries 100% of the time. I still believe it's harder to capture all the queries if you put the SQL code in the application than it is if you make them available as stored procedures. Also, if they were stored procedures, you could then know how many there are and the names of those procedures. Putting them in the .Net code gives you doubt about the quantity and if you ran the same query as before (but with different parameters) or a completely different query.
February 6, 2012 at 7:39 am
cengland0 (2/6/2012)
GSquared (2/6/2012)
Running the SQL code inline, instead of in procs, doesn't actually help with that. Well, unless the people stealing it have never heard of Profiler or traces, of course.True but it does make it more difficult and you need have elevated permissions to do that on the SQL Server. Also, this will only give you queries that were used during the start and end of your trace. It may not have caught all possible report queries if those reports were not run during that time.
I like to make it as difficult as possible knowing that it's impossible to hide all the queries 100% of the time. I still believe it's harder to capture all the queries if you put the SQL code in the application than it is if you make them available as stored procedures. Also, if they were stored procedures, you could then know how many there are and the names of those procedures. Putting them in the .Net code gives you doubt about the quantity and if you ran the same query as before (but with different parameters) or a completely different query.
Security through obscurity.
It should be noted that it is usually easier to steal assemblies than whole databases so cracking the obscuration could be done offline. I find that from a theoretical and technical point of view your position is difficult to justify, however, sometimes practicalities alter that.
Of course, it is totally your choice and most, if not all, of us here do not know or necessarily understand the nuances of you situation.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 8, 2012 at 8:16 am
cengland0 (2/6/2012)
GSquared (2/6/2012)
Running the SQL code inline, instead of in procs, doesn't actually help with that. Well, unless the people stealing it have never heard of Profiler or traces, of course.True but it does make it more difficult and you need have elevated permissions to do that on the SQL Server. Also, this will only give you queries that were used during the start and end of your trace. It may not have caught all possible report queries if those reports were not run during that time.
I like to make it as difficult as possible knowing that it's impossible to hide all the queries 100% of the time. I still believe it's harder to capture all the queries if you put the SQL code in the application than it is if you make them available as stored procedures. Also, if they were stored procedures, you could then know how many there are and the names of those procedures. Putting them in the .Net code gives you doubt about the quantity and if you ran the same query as before (but with different parameters) or a completely different query.
Assuming the database is on a server the person trying to steal your code has access to, all they have to do to steal your code is start a trace, put the application through its paces, and then use the trace to replicate your database code. This is actually easier if all the code is in the application, because then the trace will show the whole T-SQL script, instead of just proc names and parameter values.
If they don't have access to the server, then it's a moot point. They won't be able to start a trace, but they also won't be able to view proc definitions.
Security through obscurity is a placebo to make people feel safer. It's not actual security, it's an illusion to make someone who doesn't know how security really works more comfortable.
The best bet is, if the database is on their server instead of yours, assume they know everything about it. Since it's impossible to stop them from finding out, it's better to assume that and then take steps in the legal world (contracts, NDAs, et al) to safeguard your IP, instead of trying to hide what can't be hidden from a competent person.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2012 at 9:41 am
GSquared (2/8/2012)The best bet is, if the database is on their server instead of yours, assume they know everything about it. Since it's impossible to stop them from finding out, it's better to assume that and then take steps in the legal world (contracts, NDAs, et al) to safeguard your IP, instead of trying to hide what can't be hidden from a competent person.
Sure, contracts and NDA's stop all sorts of piracy. Nobody ever steals software when the terms specifically state you can't. Said sarcastically of course.
I still have contracts (which include the NDA) but I still go the extra step to make it more difficult to capture the code. Do you think I'm going to far to protect my intellectual property?
Generally, when quoting a price to a client, several factors are involved. Will they own the code after the product is delivered or will I? Can I resell the product to other companies or do they have an exclusive right to it? Will I be paid hourly or an agreed upon payment amount? The programming method all depends on these simple questions. If they are going to own the code, what do I care about piracy?
February 9, 2012 at 3:02 am
GSquared (2/8/2012)
...Assuming the database is on a server the person trying to steal your code has access to, all they have to do to steal your code is start a trace
...
If the server security has been compromised all is lost!
I assume any hacker targeting my app is trying to stole the data, not the app.
I'm just trying to:
1. Stop unautorized access to servers;
2. Stop SQL inject from people dont have access to servers.
February 10, 2012 at 7:09 am
cengland0 (2/8/2012)
GSquared (2/8/2012)The best bet is, if the database is on their server instead of yours, assume they know everything about it. Since it's impossible to stop them from finding out, it's better to assume that and then take steps in the legal world (contracts, NDAs, et al) to safeguard your IP, instead of trying to hide what can't be hidden from a competent person.
Sure, contracts and NDA's stop all sorts of piracy. Nobody ever steals software when the terms specifically state you can't. Said sarcastically of course.
I still have contracts (which include the NDA) but I still go the extra step to make it more difficult to capture the code. Do you think I'm going to far to protect my intellectual property?
Generally, when quoting a price to a client, several factors are involved. Will they own the code after the product is delivered or will I? Can I resell the product to other companies or do they have an exclusive right to it? Will I be paid hourly or an agreed upon payment amount? The programming method all depends on these simple questions. If they are going to own the code, what do I care about piracy?
You're missing the whole point of what I wrote.
No point continuing this. If fake security and protection makes you feel better, and sarcasm is your approach to an attempt to help you, then you're on your own. Ignorance is bliss, I guess. Stay blissful, dude!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2012 at 7:16 am
jcb (2/9/2012)
GSquared (2/8/2012)
...Assuming the database is on a server the person trying to steal your code has access to, all they have to do to steal your code is start a trace
...
If the server security has been compromised all is lost!
I assume any hacker targeting my app is trying to stole the data, not the app.
I'm just trying to:
1. Stop unautorized access to servers;
2. Stop SQL inject from people dont have access to servers.
We're not talking about a server security issue. We're talking about selling a database to someone, they host it on their server, and he's trying to prevent them from stealing his code when it's on their server being administered by their DBA.
My point is simply that protecting the code when it's on their server is impossible. Can't be done. Better off assuming it can't be done and taking steps to make sure they won't profit by it than taking placebo steps to "protect" what can't be protected. Obscuring it is wasted effort. Feeling that it's safeguarded when it actually isn't is just lying to yourself.
It's not about a hacked server. That's a different situation entirely.
Completely agree with you, if that were the subject at-hand here. But it's not.
Stopping injection is a matter of parameterized queries. Those will be in the data access layer (database API), whether that's stored procedures in the SQL Server engine, or is in a layer even higher than that, is nearly immaterial. What matters is never executing the contents of a form field or anything like that. You are correct about that, definitely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 13, 2012 at 3:23 am
GSquared (2/10/2012)
...My point is simply that protecting the code when it's on their server is impossible. Can't be done
...
Sure, even a hardware token can be cracked.
The only way to "sell" a software without these security issues is by offering it as a web service.
(and for a web app there are others security issues)
February 13, 2012 at 3:35 am
jcb (2/13/2012)
GSquared (2/10/2012)
...My point is simply that protecting the code when it's on their server is impossible. Can't be done
...
Sure, even a hardware token can be cracked.
The only way to "sell" a software without these security issues is by offering it as a web service.
(and for a web app there are others security issues)
Here in the UK there was even a legal case that disallowed by law the use of software timebombs i.e. you cannot disable software.
If I recall correctly, this was software used by a council for which there was a commercial disagreement with the software supplier. The software supplier got to a point in the negotiations where they said *paraphrasing* pay up or the software will stop working *paraphrasing*. The courts decided that this was an unfair practice. I cannot recall if the deactivation was done remotely, on-site or by default due to not installing a "keep alive" update.
Before anyone mentions it, timebombs were deemed acceptable in evaluation software as they specify it in advance and ensure the user agrees to the condition of use.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 13, 2012 at 6:39 am
jcb (2/13/2012)
GSquared (2/10/2012)
...My point is simply that protecting the code when it's on their server is impossible. Can't be done
...
Sure, even a hardware token can be cracked.
The only way to "sell" a software without these security issues is by offering it as a web service.
(and for a web app there are others security issues)
Exactly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 13, 2012 at 8:01 am
Heh... there are a lot of tried and true methods available in SQL Server that seem to be going by the wayside in favor of "slick" stuff. A good example of this is the PIVOT operator. A lot of folks became enamored with it simply because it was new and "cutting edge". Many folks don't even know the old CROSS TAB technique even exists even though it's usually faster code and, IMHO, easier to read and understand.
Although I absolutely agree that you have to understand and delve into the new stuff, I many times feel that we should "Teach new dogs old tricks". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2012 at 8:08 am
Jeff Moden (2/13/2012)
Heh... there are a lot of tried and true methods available in SQL Server that seem to be going by the wayside in favor of "slick" stuff. A good example of this is the PIVOT operator. A lot of folks became enamored with it simply because it was new and "cutting edge". Many folks don't even know the old CROSS TAB technique even exists even though it's usually faster code and, IMHO, easier to read and understand.Although I absolutely agree that you have to understand and delve into the new stuff, I many times feel that we should "Teach new dogs old tricks". 😉
It's a two way street though, really.
I work with several "old dogs" that haven't kept their knowledge up to date. I'm happy to learn all I can from them and occasionally come across a technique that I haven't used before (although in the last 6 months or so that has started to get much rarer), however they don't seem all that interested in learning the newer techniques either from me or from other sources. For me, it's all about using the right tool for the job.
Viewing 15 posts - 61 through 75 (of 92 total)
You must be logged in to reply to this topic. Login to reply