We have a VB.Net 2005 application that uses SQL CE 3.1 as its embedded database.
Frequently in the application, we must store strings with apostrophes, quotes, and all kinds of other stuff. It's totally unwieldy to try and manually escape every nonstandard character in every string... this is why we need to know how to handle this issue for all possible input.
What is the best method we can use to store any string, no matter what characters occur in it? The reason we must now improve our string handling is that we are now being required to store MD5 hashes of files for security and duplicate file avoidance, and these hashes usually break our import functions.
We normally enclose strings in single quotes ('). But, with the hashes as mentioned above, none of our current code works. Again: how can we be certain that the exact string we pass in will be stored in its current form, no matter what the characters?
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table. -- GETALLWORDS(@cString[, @cDelimiters]) -- Parameters -- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS. -- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. -- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. -- Return Value table -- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character. -- Example -- declare @cString nvarchar(4000) -- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.' -- select * from dbo.GETALLWORDS(@cString, default) -- select * from dbo.GETALLWORDS(@cString, ' ,.') -- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions CREATE function GETALLWORDS (@cSrting nvarchar(4000), @cDelimiters nvarchar(256)) returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint) begin -- if no break string is specified, the function uses spaces, tabs and line feed to delimit words. set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10)) declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 0, @nEndString = 1 + datalength(@cSrting) /(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while charindex(substring(@cSrting, @k, 1), @cDelimiters) > 0 and @nEndString > @k -- skip opening break characters, if any set @k = @k + 1
if @k < @nEndString begin select @wordcount = 1, @BegOfWord = @k, @flag = 1 -- count the one we are in now count transitions from 'not in word' to 'in word' -- if the current character is a break char, but the next one is not, we have entered a new word while @k < @nEndString begin if @k +1 < @nEndString and charindex(substring(@cSrting, @k, 1), @cDelimiters) > 0 begin if @flag = 1 and charindex(substring(@cSrting, @k-1, 1), @cDelimiters) = 0 begin select @flag = 0 insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cSrting, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word end if charindex(substring(@cSrting, @k+1, 1), @cDelimiters) = 0 select @wordcount = @wordcount + 1, @k = @k + 1, @BegOfWord = @k, @flag = 1 -- Skip over the first character in the word. We know it cannot be a break character. end set @k = @k + 1 end
if charindex(substring(@cSrting, @k-1, 1), @cDelimiters) > 0 set @k = @k - 1 if @flag = 1 insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cSrting, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- last word end
I am pleased to offer, free of charge, the following string functions Transact-SQL:
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character. RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps). OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides. CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters. STRFILTER(): Removes all characters from a string except those specified. GETWORDCOUNT(): Counts the words in a string. GETWORDNUM(): Returns a specified word from a string. GETALLWORDS(): Inserts the words from a string into the table. PROPER(): Returns from a character expression a string capitalized as appropriate for proper names. RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search. ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999). ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
More than 5000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs Transact-SQL please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
I have two servers: progress as transational server and mssql as warehouseserver.I did DTS that "pumps" data from progress to mssql (via ODBC). Copying thedata has to be done once a day, but sometimes there is a need to do it onuser's demand.I'd like to ensure that I properly understood the method of running DTS I'vejust found using Google.As I can see, there are at least two methods (except of scheduling):- using dtsrun - which requires user running the DTS to have adminprivileges to use xp_smdshell- using sp_start_job - which requires creating a job prior to running theDTS.I think of using the second one as it seems to be more secure - am I right?Are there any hidden traps? What else should I do?--PL(remove "nie.spamuj.bo.w.ryj" from my email address)
I am trying to persuade our security people to allow access to RS from external facing web sites. The web sites will talk to the SOAP interface over SSL so the firewall will need to open port 443 between the web server in the DMZ and the RS server inside the firewall.
Any credentials passed will be encrypted, so the main questions is around parameters to reports and the possibility of SQL Injection attacks. I need a white paper or other document that will convince them that RS will not allow SQL Injection or scripting attacks and properly validates parameters.
Can anyone tell me how I can copy store procedures from SQL Server 2000 to Source Safe 6.0? I right-click the store procedure and cop y it but when I got to the visual source safe it will not work. I therefore went through the soruce code control on Tool menu which I cannot see such commands. I do really need your help.
I've recently planned to make a program that uses remote MSSQL connection. I'm not such an expert in security. Please can someone tell me if it safe? and how can I improve my security. I know how to block SQL injection, but me fear is that some will hack my software for personal use.
I have a table that stores a value called "LocationNumber" as a varchar. I didn't create the database, I just use it. Anyway I usually sort the list by Casting the LocationNumber to an Integer. If I don't cast it, it trys to sort it lexiographically i.e. 0, 1, 21, 3, 345, 9 instead of 0, 1, 2, 3, 9, 21, 345.
It works 99.9% of the time, but the other 0.1% of the time it runs into some text that somehow made its way into the database field, and it causes an error.
Is there a way to safely cast a number in SQL? So that it just returns 0 when the value is not a number.
To show fields names I thought to read columns names.
It's ok if columns is named like "Name", "Age" etc. But if the columns is named [Country, Address, PostCode] then, I think, it can course some problems. Am I right?
First problem I thought about - changing database in the future (Now MS SQL 2k to MySQL etc.)
Is this the only problem?
To solve this I think using table which store syscolumn names as user defined columns names.
My system is speed critical and using this I would get less performance.
Which way should I go?
Case saving columns names in table, how to generate safe column name from user specified name, which can have special charters.
I'm in the process of locking down our SQL Server development environment and wanted to implement some type of version control, not just for stored procedures but for all database objects (tables, triggers, etc.)
A while back I read an article that explained how to utilize Visual Source Safe to establish version control for databases. If I recall correctly, I believe it had something to do with Visual Interdev and creating some project with Visual Interdev.
If anyone can provide alittle more insight into how to accomplish this, or at least point me in the right direction, it would be greatly appreciated.
Hi,has anybody knowledge about the safetyness of encrypting stored procs inSQL-Server 2005 using WITH ENCRYPTION? Or can they be hacked with the sameold tools which exists for SQL 2000?thanks,Helmut
Hi everybody. One of department want to place db application from one server somewhere else on corparation network for 3 month period. Problem is they want to keep it with max security. Company does not want set up another server , so we want to create another Instance and place database on it. 1. Is safe to keep 2 instances on same server ? 2. How remove access to this instance from members of local and domain admin role(If we deny acces to BuildInAdministrators how it is going to affect security?) Thank you
I have been using the index tuning wizard to review some of my stored procs,and views. So far most of my indexes have been set up well, but I am curiousas to how they would look under a production system load. I was thinking ofrunning a profile for about 30 minutes or so on the prod system, and thenusing that profile for the index tuning wizard to see what it says.Would this be of value?Can running a profile on a prod system be dangerous?--BV.WebPorgmaster - www.IHeartMyPond.comWork at Home, Save the Environment - www.amothersdream.com
AD corrupt on RAID caused SBS 2003 to require reinstalling, no back ups.
Due to space constraints, database was attached to the D: drive so all is lost on the OS but the LDF and MDF files are safe on the other drive. Since a complete re-install of SBS 2003 is required I'm going to go with SQL 2005. How can I re-attach the old database to the new install?
My understanding is that Microsoft SQL Server 2005 (unlike SQL Server 2008) is NOT safe for image-based deployments or at least is not officially supported. Is anyone aware of what tasks need to be performed if an image-based deployment approach is taken?
I have Windows and SQL Server installed on HostA, and I want to create an image and install a copy of the image to HostB, HostC, and so on.
When the image has been restored to the target machine, I do the following: 1. Run NewSID.exe 2. Change Host Name 3. Change IP Address 4. Change the SQL Server sysservers table via sp_addserver.
I noticed that the SQL Server installation program has created sevral Windows groups and SQL Server has several logins, users, and schemas with names associated with the source machine, HostA.
My question are: What are these accounts used for? Can or should they be renamed (this shouldn't affect the SIDS, right)? Is there anything else that requires changing? Can this be automated? What "gotchas" should I look for? Or is this simply not "doable"?
Hopefully this is the right forum! Im guessing they both are since they return the values pertaining to the current scope. I don't want to use transactions when I dont have to. What do you guys think?
Assume in a table, I have a table structure which contains parentID and childID.
In my procedure to insert a child for example, I begin a transaction, do a select statement see how many children for the given parentID, if is more than 10 then don't insert , otherwise insert the new child in. Then commit the transaction.
I wonder how thread safe such code would be... Quite worry about data corruption.
If 2 threads both updating this table, and the select statement both return 9 children, so both of them think they can do the insert, so the parent will end up with 11 children. Is such scenario possible to occur? If so, how to solve it and make sure the code is thread safe?
I have always upgraded the OS on test servers before upgrading on production. We are in the process of upgrading hardware for a production server and are considering installing Windows Server 2003 Standard Edition vs. what previously existed on the server, Windows 2000 Standard Edition. How much of a risk would this present? I'm leaning towards not changing the OS until we have a chance to upgrade the test servers first.
Hi - I am wondering if anyone can give me good advice on the following situation:
I'm a new employee at a place where the SQL Server/Visual Source Safe admin has left. Only his co-worker has a VSS account and the VSS admin never gave her the admin p/w and he cannot be contacted. We need to have an account set up for me.
My question is: How can this be done with the knowledge of the admin p/w? Would we have to backup the files, un-install VSS, re-install it and set up the admin account again, or have I answered my own question? Or are we screwed? I don't have enough experience with VSS to make a decision. At this point we're considering calling "Geek Sqaud" or "Geeks on Call", etc.
My manager has asked me to install and configure Source Safe so that our team can check out stored procedures, (and other objects), and check them back in when we are finished working on them.
I'm not familiar with Visual Source Safe. Does SQL Server 2005 work with Visual Source Safe in such a way that I can check out/in SQL Server objects such as procedures?
If someone knows the answer, or perhaps an existing thread that covers this, please let me know.
Would turning off ViewState in Report Manager be safe? Or at least is there a way to deactivate ViewState for viewing reports in Report Manager instead of all pages in the web site?
I'm not sure of the impact of deactivating this with the internals of the web application.
To implement the new SQL 2005, I plan to make the environment easy to manage. The environment should be simple to document and be automated via scripts. Therefore I plan to use mount points as described below.
On a typical SQL server with multiple drives like C, D, E, F, G, H. Where each drive will have various folders to hold SQL code, data files, transaction log files, tempdb files, snapshot files, and other types of files. This typical environment is not pretty and is hard to write scripts for.
So I plan to standardize on one standard directory structure via volume mount point. On all new SQL 2005 servers, we should see drive E as the one and only SQL Server directory. Other drives will be mounted to drive E as shown.
E:
SQLSERVER local folder -sql code for each db instance
SQLSHARED local folder -sql shared tools for all db instances
SQLTLOG1 Drive H -db transaction log
SQLSNAP1 Drive F -db snapshot files
SQLTEMPDB1 Drive H -tempdb main data file
SQLWORK Drive D - DBA work area
SQLDATA1 Drive G -db data files
SQLDATA2 Future Drive -if SQLDATA1 is too large for any direct attached drive, or to get more I/O throughput.
With this implementation, I can easily write scripts to manage the environment. Also if any mounted volume is out of space, we can swap the based drive without doing any change to database configuration. We can also switch from direct attached drive to SAN in the future.
Do you think mount point is safe to use with SQL 2005? I know it is supported.
Do you have a standard directory structure for your environment? How do you do it?
If I send multiple sql's with ado.net in one statement (one executeSql separated by semilcolons), and the second one fails, will the first one be rolled back? or do I need to put it all in a transaction?
I plan to use Log Shipping on SQL 2000 to have warm standby database. I understand current procedure but have a question wether I can do full normal backups of my database server without screwing up my Log Shipping process? Also can I do transaction log backups as well (separate from the ones used for log shipping)?
I had created a Integration services project within my local system.All my packages are running fine.I added it to source control.Now i added this project from source control to another machine.It is failing to run...The path it is trying to execute is the location of the where i actually created my project.
Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure. I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Our application drives SSIS packages from ASP.NET web services. To alleviate some of the package load time overhead application caches SSIS Application object and several instances of "pre-loaded" packages in ASP.NET Application context. As needed the code uses cached SSIS Application instance to execute "pre-loaded" packages. Is this thread-safe?
I have to install MDS on a production server without testing on test server (there is none test/dev server) On the production server each day are rendering SSRS reports which cannot be interrupted.
What risk is by installing MDS on a production server, (the SSRS, SSIS and engine may not go down,well can for some hours) SQL2012Enterprise.
What do I have to do first, steps taken, to install as save as possible for the current running BI environment?
The goal is to address visual source safe database on the network. We have the srcsafe.ini in the network as \ipaddrsrcsafe.ini. Now I create a new VSSDatabase object and call its OpenDb. Well for simple consle app or winform it is ok. But I was running it under Sql server Stored Procedure. It failed for I cannot access the source safe path throgh the COM object.
I know it is because of Windows identity. So I add the following code before I want to open the database, changing the to the WindowsIdentity: WindowsIdentity impersonId = SqlContext.WindowsIdentity; WindowsImpersonationContext orgCtx = null; try { orgCtx = impersonId.Impersonate(); VSS_Database = new MVSI.VSSDatabase(); // VSS_Database.ImpersonateCaller = true; VSS_Database.Open(Path, UserName, PassWord); } catch (Exception err) {
Without the commented line "// VSS_Database.ImpersonateCaller = true", this does not work at all. It just behave like no changes to the windows identity. However if I add this code, well, OpenDb will result in a No-response query. The Sql server is running the query with no responses. Have you ever met that before? I am really frustrated. Thanks
CLR's not registered as SAFE are disallowed on our Network. It appears that the key associated with the assembly (ISSERVER) may be able to link to the Service Master Key. If the service master key is created with a (CA) Certificate Authority, is it possible to create the SSISDB that accompanies the Integration Services Catalog as a SAFE assembly?
[adding] BY CA meaning:
ALTER SYMMETRIC KEY [mykey] ADD ENCRYPTION BY CERTIFICATE [myservercertname]
