SQL Server 2014 :: Sanitizing Inputs When Creating String By Concatenation
Mar 19, 2014
I have a need to create a table in a sql server database from C# code. The kicker is that the user must be able to specify the table and field names via the UI. I can do a bit of sanity checking but as long as they enter something reasonable I need to accept it. Normaly I always ADO parameters to sanitise any user parameters but they can't be applied to table and field names, only values. As far as I'm aware that leaves me needing to concatenate strings and that's something I usually avoid like the plague due to risk of SQL injection.
My actual question : Assuming string concatenation is my only way forward, how can I sanitise the values that would go into the table name and fieldname bits of a CREATE TABLE statement to ensure that injection can't occur? I've been pondering it and I think I just need to check for semi-colons. Without a semi-colon I don't think a user could inject an extra statement could they?
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
------------- case when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%' and [New Extension or Replacement Audit Flag] like 'FLAG%' then 'Reject – New, Extension, Replacement invalid entry' --'it is jp' else '' end as [ES Fully approved data 1], case
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like Create proc create procedure create proc create view create function
I need its result as:
Alter Procedure Alter Procedure Alter Procedure Alter View Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
create procedure ChangePassword(@sUser char(20),@sPassword char(20)) as begin execute immediate 'GRANT CONNECT TO ' + @sUser + ' IDENTIFIED BY ' + @sPassword grant execute on ChangePassword to public end
I m getting syntax error at '+' sign. I saw in BOL and it is exactly the same. Can nyone help me out?
create procedure CheckSQLErrors( @TheCode integer, @TheState integer, @Routine varchar(40), @Help varchar(40)) as begin { call LogMsg('SQLA',@Routine,@Help,'sqlstate=' + @TheState + ', sqlcode=' + @TheCode) } end
I m getting this error. "Incorrect syntax near + "
+ is used for string concatenation. I tried to use CAST to convert @TheState and @TheCode variables to varchar but did not work. Can you help me out?
FYI LogMsg is a sproc
create procedure dbo.LogMsg( @aAppName varchar(18), @aRoutine varchar(20), @aType varchar(5), @aMsg varchar(255)) as begin insert into MessageLog(strAppName,strRoutineName,strType,strMe ssage) values( @aAppName,@aRoutine,@aType,@aMsg) end GO
I have a sitaution here where I need to convert some relational data to a flat file. I have a primary record that flatens out pretty well with the exception of two columns that need to have row data converted to strings via concatenation. The column size is Char(146) . I attempted to use 2 cursors to create the strings.
C1 --outside cursor to pull unique record id (161,000+ records)
C2 -- SELECTs the top 29 secondary (relational) records for each C1 rec (FIELDX as Char(5))
FIELDX is the concatenated up to 29 times and inserted in to a flat table based on record id for flat file export.
The issue is that this takes FOREVER to run and the 3Ghz XEON w/2GB Ram server weeps.
Declarations are as follows:
DECLARE C1 CURSOR FAST_FORWARD READ_ONLY FOR SELECT Distinct Record_ID FROM tblProcedure
DECLARE C2 CURSOR FAST_FORWARD READ_ONLY FOR --need only the top 29 relational records to string out SELECT TOP 29 Cast(pr_icd1 as Char(5)) FROM tblProcedure WHERE Record_ID = @RECID --From C1
OPEN C2
SET @tmpICDstr = ''
FETCH NEXT FROM C2 INTO @tmpICDchar
WHILE @@FETCH_STATUS = 0 BEGIN
SET @tmpICDstr = @tmpICDchar + @tmpICDstr
FETCH NEXT FROM C2 INTO @tmpICDchar
END
--'INSERT INTO [Validation].[dbo].[tmpICDStr] (RECID, sg) VALUES (@RECID, @tmpICDstr) --'INSERT INTO @tmp (RECID, STRsg) VALUES (@RECID, @tmpICDstr) SET @tmpICDchar = ''
Anybody have a suggestion on how to speed this up. I am looking at about 1min/100 C1 records. Do the math for 161,000+ C1 records. Ugh.
Hi,How can I remove a part of string from complete string in SQL?this is something i want to do:update aceset PICTURE = PICTURE - '\SHWETABHShwetabhI'But here ' - ' is not allowed.How can I remove the string \SHWETABHShwetabhI from the columnPICTURE?Regards,Shwetabh
Dear GroupJust wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')Thanks very much for your expertise and efforts!Best Regards,Martin
I guess I'm the only one with this problem -- couldn't find anything on it in the back questions. Maybe it's a weird problem. :)
Anyway, although I'm not new to SQL, I am a bit new to stored procedures, and MS SQL Server 7. (I've been using mySQL, decent, but doesn't have many features ... )
I used some ASP and stored procedure code from 4guysfromrolla.com for session tracking through SQL Server.
I've modified most of the stored procedures so that they actually work. :)
To answer some questions before they're asked: It's a resume database, and does need to be able to store 8000 characters at a shot. (I'm hoping 8000 is as large as it gets for this particular field.)
There's only one problem now: One of the stored procedures enters information into the sessionvalue field of the table. However, much of our data contains apostrophes ('), and we need to be able to store them. I thought that modifying the execute statement would do it, something like:
Hi,I'm trying to concatenate a Description (nchar(100)) and Date(datetime) as Description and my initial effort was just"...description+' '+open_date as description..." which throws a date/string conversion error; finally came up with a working string belowbut don't think it's the optimal way to do this - any suggestions?select (rtrim(description)+''+rtrim(convert(char(2),datepart(mm,open_date)))+'/'+convert(char(2),datepart(dd,open_date))+'/'+convert(char(4),datepart(yyyy,open_date))) as description fromoncd_opportunity where opportunity_id=?open_date is not a required field at the db level, but it is requiredon the form so it should not be null as a rule.
I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters. Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1, [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2 CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex] ( @CustomProp varchar(max),
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
Everything goes on fine until the SQL Server Database Sersvices configuring and a pop-up says the following:
SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account.
I have no idea how to come over this. Any help would be appreciated.
PS I don't know what AGTACCOUNT or AGTPASSWORD is, so maybe the error might be there. If not, anybody willing to explain to me what it is, it would be GREATLY appreciated.
Hi,I am trying to concatenate 2 text fields to update a sql db column byusing:UPDATE MessagesSET private_messages = private_messages || @newmessageBut the stored procedure I am using doesn't accept ||, I've also triedCONCAT() and CAT() but so far this has failed. Is there another way Ican do this?Thanks in advance,Kevin
Is there a limit to how much text you can concatenate (other than datatype limits, of course)? For example, if you write:SELECT 'This is some text from: ' + Convert(char(15), SomeFieldName01)+ 'some more text' + Convert(char(25), SomeFieldName02) + 'yet moretext.' As BigNoteFROM TableIs there a limit on how much you can concatenate into that big notefield, meaning is there a limit other than the size of the data type?
I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).
product details are stored in `[products]` articlegroups are defined in `[articlegroups]` and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`
Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:
Now I can get the separate fields via a statement like this:
SELECT ga.slug_nl as slug_nl_0 FROM articlegroups ga INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id INNER JOIN products gp on gp.id=pcm.artikelid WHERE gp.id=2481446
We have a mobile device project, which has a database file (Database.sdf) as part of the project. I am trying to connect to it to insert a record. The syntax I'm using is:
I have tried various connection strings such as "data source=MyDocumentsDatabase.sdf"; and "data source=C:MyProjectsThisParticularProjectDatabase.sdf"; << the connection string in teh server explorer
and "data source=MyAssemblyName.Database.sdf"; and "data source=MyAssemblyNameDatabase.sdf";
I've also tried getting the path the the executing assembly and adding that in, but still no luck.
but they all throw the error: The database file cannot be found. Check the path to the database. [ File name = Database.sdf ]
I have searched the internet and found no explanation about how the path to the Database is made up (ie is it the path on *my* computer, or the virtual mobile device?) and how I can actually get the path the the database without hardcoding it.
I have a table with a startdatetime and an enddatetime column such as:StartDateTime EndDateTime what I want to see returnedis:01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/20041:30PM"01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/200410:30AM"Maybe someone has accomplished this aready in a stored procedure andhas an example of how to do it?lq
I am ultra new to this so thanks in advance for any help. I was trying to create a connection to a database that I created in SQL Express. I am essentially trying to submit three attributes to the existing database from a table that consists of three textboxes and a submit button. I would like all of the code to be in the head of the page (because that is the standard here) so I wanted to know what the connection string should be in Visual Basic 2005 Express to establish a connection on the same machine. I'm not sure about the connection string, but I am also not sure about a lot of the code. Also, the Using clauses seem to give me an error (where should it go?). This is what I have in the head of the page (visual C# by the way). Also, I got this from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx : Using System.Data;Using System.Data.SqlClient;protected void Button1_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection("Data Source=(local);Database=CustomersDB;Trusted Connection=true");conn.Open();string insertstring = @" insert into Catagories (CustomerID, CustomerName, CustomerEmail) values (" + textbox1.value + ", " + textbox2.value + " + ", textbox3.value");SqlCommand cmd = new SqlCommand(insertstring, conn);Sql.ExecuteNonQuery();conn.close();} If there is absolutely any insite into the problems with my code, I really appreciate it.
