|
Reducing the attack surface of your application is a good thing, but there are many other things I would recommend before getting to stored procedures:
* do not use sa to login
* encrypt your database connection string
* use parameterized queries
The efficiency/speed argument is bogus, especially considering murphy's law. In the time you'll take to finish the average 6 month project, processors will have improved enough to make the small "precompile" argument meaningless. The same could be said of many techniques for improving performance. Just let it go, and concentrate on creating a well structured application that is easy to maintain. Performance is a concern, but one more likely to be solved by a well placed index.
I'll admit that stored procedures are a useful abstraction for programmers that do not break down their applications into multiple well defined areas of responsibility. However, if you are in the habit of creating multi-tiered, well structured applications:
* SQL is in one place, or sometimes there is no SQL at all (O/R Mapping).
* Code is easily ported to different database platforms (again, the SQL is in one component that can be swapped out or regenerated)
* There is a layer of abstraction between the business code and the database already
I am content to let people use stored procedures if they want to. Most applications have bigger design problems than the decision whether to use stored procedures or not.
my blog
|
|
|
|
|
Steven Campbell wrote:
* do not use sa to login
* encrypt your database connection string
* use parameterized queries
The first and third of these I actually discussed in a blog entry of mine about a week ago so I am well aware of them.
Talking about Murphy's Law: Encrypting a connection string can be more trouble than it is worth. A project I saw recently had encrypted strings, but eventually comments started to appear next to them showing the string in decrypted form to make them easier to work with (it defeats the purpose really - Also, it is better to design a security system that people will not be inclined to break in order to do their everyday jobs). The other problem with encrypted connection strings is that you have to store a key to decrypt them somewhere. Better to use a trusted connection then it doesn't matter whether you know what my connection string is or not as you will still need to know the name and password of a trusted account and access it through a trusted domain.
Steven Campbell wrote:
I'll admit that stored procedures are a useful abstraction for programmers that do not break down their applications into multiple well defined areas of responsibility
Even if the application is broken into well defines areas I still think it is better to use stored procedures because they allow extra boundary checking. There are instances where a value on a record must be cross checked against other date to ensure validity. Constraints on tables can only go so far, for more complex checks SPs are better.
Again, security comes into play here as more security checks can be performed, even if they should have been performed elsewhere you cannot guarantee that another part of the application has not been compromised already.
Do you want to know more?
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Steven Campbell wrote:
* do not use sa to login
I strongly agree. That's one of the reasons I like sprocs: I can create a user that has only execute permissions on the sprocs, no direct select, insert, update (much less schema change) permissions.
Steven Campbell wrote:
* encrypt your database connection string
One point to make here: no matter how you encrypt/protect your connection string, your application has to have the ability to decrypt it. So it's inherently vulnerable. It has to be, or your application could not read it. Certainly: it adds another layer of protection I am not sure it's worth the effort.
Just my thoughts...I certainly cannot quantify any of this.
Bill
btw: I have enjoyed reading this thread. Thanks!
|
|
|
|
|
I won't go in to the optimization discussion, but another way to do the same query without the case statements in the where clause is as follows:
CREATE PROCEDURE GetProducts (<br />
@Name varchar(20),<br />
@Status int = NULL,<br />
@FromTime datetime = NULL,<br />
@ToTime datetime = NULL)<br />
AS BEGIN<br />
SELECT<br />
[Name],<br />
[Status],<br />
[RegistrationTime]<br />
FROM [Products]<br />
WHERE [Name]=IsNull(@Name, [Name])<br />
AND [Status]=IsNull(@Status, [Status])<br />
AND [RegistrationTime]>=IsNull(@FromTimestamp, [RegistrationTime])<br />
AND [RegistrationTime]<=IsNull(@ToTimestamp, [RegistrationTime])<br />
ORDER BY [RegistrationTime]<br />
END;
Hope this helps.
Jeremy Oldham
|
|
|
|
|
Thanks. This is of course preferrable syntax. But I ran some tests and figured out that optimization really sucks. So I will go back to specialized stored procedures.
Вагиф Абилов
MCP (Visual C++)
Oslo, Norway
Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski
|
|
|
|
|
Hi All,
Can anybody tell what is the equivalent of "For Update" Statement of oracle in Sybase.
or
It would be very helpfull if u suggest/commemt on this
I 'am using vb.net and syabse. How to handle row locking while updation ?
Thanks in advance.
Vinay
|
|
|
|
|
Does anybody dare to take the this challenge.......
|
|
|
|
|
I used oracleclient to call a storedprocedure, It always display "{"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'RK001_1'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n" }" error.
Can give me a sample that it have some input parameters and output parameters(ref cursor)? Or tell me how to solve the question.
Thanks.
|
|
|
|
|
public DataSet GetEntries()
{
//top 5
string ConnectionString = ConfigurationSettings.AppSettings.Get("connString");
SqlConnection SQLConnection = new SqlConnection(ConnectionString);
string SQLView = "CREATE VIEW Content_View AS "
+ "SELECT TOP 10000 [HJ_BLog].* "
+ "FROM [HJ_BLog] "
+ "ORDER BY Date DESC ";
SqlCommand SQLCommandCreateView = new SqlCommand(SQLView, SQLConnection);
SqlDataAdapter DataAdapter = new SqlDataAdapter(SQLCommandCreateView);
DataSet UserSet = new DataSet();
DataAdapter.Fill(UserSet);
SQLCommandCreateView.Connection.Close();
string SQLQueryTop2 = "SELECT TOP 5 [Content_View].* "
+ "FROM [Content_View] ";
SqlCommand SQLCommandTop2 = new SqlCommand(SQLQueryTop2,SQLConnection);
SqlDataAdapter DataAdapterTop2 = new SqlDataAdapter(SQLCommandTop2);
DataSet UserSetTop2 = new DataSet();
DataAdapterTop2.Fill(UserSetTop2);
SQLCommandTop2.Connection.Close();
string SQLDropView = "DROP VIEW Content_View ";
SqlCommand SqlDropView = new SqlCommand(SQLDropView,SQLConnection);
SqlDropView.Connection.Open();
SqlDropView.ExecuteNonQuery();
SqlDropView.Connection.Close();
return UserSetTop2;
}
Pretty much using a view to sort then getting the top 5 from the view then deleting the view. Is there an easier way to do this? I am not sure the preformence hits on creating and droping views. or just a all around easier way to do this.
thanks
any points will be great
|
|
|
|
|
public DataSet GetEntries()
{
string sql = "SELECT TOP 5 * FROM [HJ_BLog] ORDER BY Date DESC";
string ConnectionString = ConfigurationSettings.AppSettings.Get("connString");
SqlConnection conn;
DataSet ds;
SqlCommand cmd;
SqlDataAdapter da;
try
{
conn = new SqlConnection(ConnectionString);
cmd = new SqlCommand(sql, conn);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
}
catch (Exception ex)
{
}
finally
{
if (da != null) da.Dispose();
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}
return ds;
}
|
|
|
|
|
Thank you, I have no idea what i was thinking when i wrote this.
|
|
|
|
|
Hi Jacob,
I have a couple of fairly phylisophical points to add. Ignore them as you please  .
First off, it's not a really good idea to change database schema from within your code. Creating and deleting view (and table and sprocs, etc) are really design-time processes. Do it before-hand and be done. Why? Well, what happens when user #2 tries to create the view before user #1 drops? You've got an exception to handle... It just get's too messy.
Related to the first point, I am very leary of a program running with a sql user that has sufficient privileges to alter the db schema.
Just my $0.02
Bill
|
|
|
|
|
Is it possible to position to a specific row in a DataTable or DataView? I have a DataGrid that displays either a detail view or a summary view and uses built-in paging. Both sets of data are from the same database table, the summary view is an aggregate (group by) with one less column than the detail view.
When the user changes views, I execute the appropriate stored procedure and re-bind the results to the grid. The problem is I need to re-display the same page of records in the new view. In other words, if the record for company ABC was the first record displayed in the grid in one view, it needs to be the first record in the other view as well. Currently if the user has paged down to page 3 and changes views, they are back on page 1.
Thanks for you help,
Mark
|
|
|
|
|
Hi Mark,
Is this a paging problem or a sql problem? I mean: do the two sprocs return the same rows in the same order? If so, can't you just grab the current page# from the datagrid (CurrentPageIndex) and then set it again before you bind to the new data?
Bill
ps: code snippets would help...asp.net datagrid ?
|
|
|
|
|
I am writing a small inventory application at home using VS2005. I have installed the latest build of MSDE on my machine, created a table, and defined the fields within it.
One of the field definitions lets me create an image field in the table.
What I'd like to know is how to save and retrieve an image (bmp) into that part of the table. Do I pass image as a string? How do I retrieve it and load it into the PictureBox? Do I just do a Convert on it?
I noticed that the SqlReader provides a means of retrieving all of the database types, except GetImage() !!
Any help would be appreciated.
This signature left intentionally blank
|
|
|
|
|
Use GetBytes to retrieve images from SQL. Once you have the bytes, it should be easy enough to get that into a picturebox.
my blog
|
|
|
|
|
Are you looking to hire a programmer to build for you a library management project? Or would you just like someone to give you such a project for free? Sounds like a big job.
On the other hand, if you were building it yourself and had a specific question, this would be a good place to ask it.
|
|
|
|
|
|
Nope, guess not.
It does seem like there are more and more of these questions - the kind where the poster just asks for a major system, and how soon can we get it to him/her. I see he/she deleted the original post. I try to be as polite as I can be with these sorts of things, and encourage these sorts of posters to ask the more specific questions that can be effectively addressed in a forum like this. But I'm starting to realize why others on the forums give up on the polite approach and just start hammering these guys.
Oh well. 
|
|
|
|
|
HI there,
I have a dataset that I'm returning from a query, the problem i'm having is how can I bind the data to a table control? I only need to render three of the columns, but I guess the configuration I need to pretty complex.
for each colour desc there is multiple quantitys * sizes, if someone could help me out with the code I would really appreciate it as I have tried nested datalists & datagrids.
colourdesc | sizedesc | sizedesc | sizedesc |
----------
value qoh | qoh | qoh |
----------
value
------------
sample data
+------------+------+------+-----------+--------+---------------+----------+
| qoh | size | shop | style | colour | colourdesc | sizedesc |
+------------+------+------+-----------+--------+---------------+----------+
| 0000000000 | 3 | 1 | 10475RIB1 | 2 | BLACK | XS |
| 0000000002 | 4 | 1 | 10475RIB1 | 2 | BLACK | S |
| 0000000000 | 5 | 1 | 10475RIB1 | 2 | BLACK | M |
+------------+------+------+-----------+--------+---------------+----------+
Sean - thanks in advance
|
|
|
|
|
I have an application (C#/ADO.NET) that collects alarms from equipment at around 100 sites, through concurrent TCP/IP listeners. Per day I receive about 1 million messages (~10/sec). I used to insert them directly into the destination table, but that would lock up the table so user access (read/reporting) was hardly possible. Now I insert the data in a intermediate table and upload it every 5 min. to the final table with SQl agent.
First of all, this doesn't look like an elegant solution. Furthermore, performance is still quite bad. Even though I have just this one DB/table, SQL-server uses up all available memory (1.5Gb out of 2Gb) and all available CPU, 24x7; I'm just wainting for a melt-down...
Anybody have any experience with this amount of transactions, and/or any suggestions?
Thanks.
|
|
|
|
|
Do you have the relevant clustered index on your table? Your table should have a clustered index so that new items are added to the end of the index and the index does not then need to be reordered.
Affordable and reliable hosting? Click here!
|
|
|
|
|
Does Sybase SQL Anywhere 8 support row lock?
I'm writing a MFC application that already implemet row lock with Oracle via ODBC.
In Oracle I use:
1. Start a transaction
2. Lock a record (SELECT * FROM Table WHERE ID = 1 FOR UPDATE NOWAIT)
3. Wait for a user record's modify
4. Commit
If another user tries to modify the record, Oracle returns an error message.
Is it possible to implement this with Sybase' server ?
Thank you
Vin.
vin
|
|
|
|
|
I'm trying to execute an SQL statement with parameters through a Command object. I've done it a thousand times through ADO.NETs SqlCommand object, but now i'm using ADOs Command object from vb6. Is it possible, cos its not working.
lEntry_ID = 5
Dim cmd As Command
Set cmd = New Command
Set cmd.ActiveConnection = gConn
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("@Entry_ID", adInteger, adParamInput, , lEntry_ID)
cmd.CommandText = "SELECT * FROM Entry WHERE ID = @Entry_ID"
If cmd.Execute.EOF then
'blah
End If
I get the error "Must supply a value for variable: @Entry_ID". Any help would be appreciated.
--
Dave
|
|
|
|
|
With VB6/ADO you do not use named parameters in the CommandText... you use positional parameters:
cmd.Parameters.Append cmd.CreateParameter("Entry_ID", adInteger, adParamInput, , lEntry_ID)<br />
cmd.CommandText = "SELECT * FROM Entry WHERE ID = ?"
More or less the same was as with the ADO.NET ODBC provider.
Regards,
Rob
--
RMTrack a new bug tracking tool. Available now from http://www.rmtrack.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
