|
Thanks guys I managed to do it in with .NET by using SqlCommandBuilder.DeriveParameters(MySqlCommand)
It was used to create a generic method to loop through values from a DTO and place them into input parameters for a given Stored procedure on the mapper layer of our code. If I cache the results (for speed) it saves us lots of time rewriting very similar stuff inside each mapper to take the dto and asign its values to known paramaters in different procedures.
So what i have is
DataAdapter myMethod(string SProcName, DTOType inputParamsDTO)
{
DataAdapter da = new DataAdapter.
[psudo] open connection to sql
SqlCommandBuilder.DeriveParameters(SProcName)
[psudo] loop
[psudo] if SProcName.Param.Direction == input OR input/Output
[psudo] if inputParamsDTO.Tables[0].Rows[0][i].Contains(SProcName.Param[i].ParamaterName)
[psudo] SProcName.Param[i] = inputParamsDTO.Tables[0].Rows[0]["SProcName.Param[i].ParamaterName"]
[psudo] end loop
[psudo] close connection to sql
[psudo] return da
}
So we pass it a SProc and a DTO (or dataset) and it gives us back a data adapter we can just calla Fill() on.
But it needs to cache some where to stop excessive round trips
|
|
|
|
|
Microsft provide a class to do just this - SQLHelperParameterCache as part of its SQLHelper class as part of its Data Access Application Block. I don't have any links to hand, but google is your friend.
Adam
|
|
|
|
|
Hi all,
I am installing SQL Server 2005 with 2000 already installed. After I go through the whole setup process now with several instances created I can't launch the program from the strt menu. It only gives me the 'configuration tools' option.
Also I think 2000 was installed as a local user but now I am installing from a domain account.
What's going on here, should I install sql using the domain account even though I only plan to use it locally?
Thanx,
Kiefie
The man with an idea.
|
|
|
|
|
You should install Sql Server under a special user acount which you make to only have the needed permissions, this secures it incase it is compramised.
Which edition of Sql Server 2000 are you installing? When you install it is there an option to install the Enterprise Manager and other tools? Thats what you use for managing the server.
|
|
|
|
|
I'm installing sql server 2005 and already have 2000 installed. That's my problem. I can install sql server 2005 but afterwards there is no sql server management studio option in the start menu, only 'sql server 2005 -> Configuration Tools'.
Kiefie
The man with an idea.
|
|
|
|
|
|
It is the full version (Enterprise) I believe.
Another fellow developer here also tried it and got the same results - no Management Studio.
I just started at this company and am trying to get my pc setup...
Kiefie
The man with an plan.
|
|
|
|
|
SSMS (SQL Server Management Studio) should be under:
Workstation Components, Books Online, and Development Tools
in the installation.
|
|
|
|
|
I may have found the cause and the solution, trying it now...
Cause : SQL 2000 was installed and then Visual Studio 2005, with VS 2005 it installs 'sql Express' so when you install sql 2005 it sees it as already installed and therefore does not update the start menu shortcuts.
Solution : Uninstall sql Express then re-install 2005.
Kiefie
The man with an plan.
|
|
|
|
|
I execute an sql-query from code (C#) via OleDb to an Access database.
But one of the values pasted in the query has a ' character resulting in an error.
How can you escape that character in the query?
eg. SELECT * from A_Communes where label_d like 'BRAINE-L'ALLEUD%';
I googled for it, but most results are for Oracle which don't seem to work for Access.
thanks.
|
|
|
|
|
You should use sqlparameters as they will help you to avoid issues with escape characters and it will protect you from sql injections.
|
|
|
|
|
Unfortunately we don't use sqlparameters and I don't think it's an option to re-write the DAL component...
|
|
|
|
|
Then you should parse all the query and replace ' with \' but you will have to do that for every escape character. But it leaves your database open to sql injections. Have a look at these links:
http://www.codeproject.com/aspnet/SqlInjection.asp
http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp
|
|
|
|
|
V. wrote: Unfortunately we don't use sqlparameters and I don't think it's an option to re-write the DAL component...
Then you should give the person that wrote the DAL a good hard slap for being an idiot.
Seriously - You need to use SqlParameters to reduce the risk of a SQL Injection Attack. No ifs, no buts, it just needs to be done.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Colin Angus Mackay wrote: Then you should give the person that wrote the DAL a good hard slap for being an idiot
It was me...  the dll is actually a very easy and stable dal component, but written before I even knew the very existance of what SQL injection was. I'll probably keep myself busy with making this better, but now is just not the time...
|
|
|
|
|
Do you have to the time to waste with problems like the one you're asking about now? Either you make your future life easier by rewriting for parameters, or you waste your time by dealing with little problems that crop up like this because you didn't use parameters in the first place.
|
|
|
|
|
Maybe you didn't mean too, but I find this reply not really constructive.
If you can't give a constructive answer, please don't waste your time writing it.
Your parameters option might be the best and I surely will keep it in mind for the future, but for now the DAL component I wrote has saved me hours and hours of time, so it can't be thát bad. (Yes I know, it probably is in your eyes.)
thank you.
|
|
|
|
|
V. wrote: I wrote has saved me hours and hours of time,
Did it now?
So now you're stuck with this problem, future problems, and if just a single attack get's through and destroys your database, how many hours of time are you going to "Save" rebuilding it?
Let me put it to you this way. Your code is going to face an attack. It's inevitable in a production environment. Where is the most likely source of an attack going to come from? The first one on your list of things to plan for are disgruntled employees, not some script-kiddies or hackers.
|
|
|
|
|
|
Dave is right. Statistically most attacks are insider jobs.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
lol, I'm not saying he was wrong, I just didn't like the tone of his reply.
You set me straight as well, but at least in a constructive way.
|
|
|
|
|
ah... okay. 
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
do it like this
SELECT * from A_Communes where label_d like 'BRAINE-L''ALLEUD%';
just place another [ ' ] the apostrophe 
|
|
|
|
|
 life can be so simple sometimes...
Thank you very much!
|
|
|
|
|
The advice you were given still leaves you vulnerable to SQL Injection Attacks. Please use parameterised queries at a minimum to reduce the risk.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
