Converting data with apostrophes in it

Question

Converting data with apostrophes in it

dwjongbloed

SSC Eights!

Points: 943
More actions
February 5, 2010 at 12:38 pm

#229672

I have data coming from one table to another table using ASP backend to insert the data I believe. The problem we are having is that some of the data has apostrophes in it and is throwing errors at us. Need to figure out how to get the data with apostrophes to be able to be inserted into the table. Below is the code that is used to do the insert into the table. ANy help on this issue would be greatly appreciated.
Code:
{
//save button
if (Page.IsValid)
{
foreach (ListItem myItem in exsisting.Items)
{
IDataReader insert = DB.getResult("INSERT INTO DM_USER_LISTS(UL_USER_ID, UL_DIMENSION, UL_ID, UL_DESC, UL_DIM_KEY, UL_DIM_DESC) VALUES('" + Request.QueryString["user"] + "','" + dimension.SelectedValue + "','" + listID.Text + "','" + listDesc.Text + "','" + myItem.Value + "','" + myItem.Text.Substring(myItem.Text.IndexOf('-')).Trim() + "')");
insert.Dispose();
insert.Close();
}

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

OK you've got a couple of things going on here...

1) it looks like you're using Ad-hoc dynamic SQL in your app to talk to SQL server. This is a really really dangerous thing to do. If I really wanted to I could put things in your input form that would do potentially very bad things to your database... For a comic representation of such have a look at this... http://xkcd.com/327/ what I would do is create a stored procedure and pass the data as parameters to the stored procedure. IT makes code reuse easier and lots of other things...

2) where's your code to check for single quotes? if you need to get the single quotes into a varchar column in the database when you need to escape them by putting 2 single quotes where there should be 1. Easiest way to do this is to loop through your string and for each single quote character replace it with 2. Best to probably put this is a misc class or something so that you can call it from other places in your application.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

If you passed the information to a stored procedure instead of using embedded SQL, this problem and some of the security problems that Luke mentioned would simply disappear.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Jeff Moden (2/5/2010)
If you passed the information to a stored procedure instead of using embedded SQL, this problem and some of the security problems that Luke mentioned would simply disappear.

Yup, I guess this was the point I was trying to make, and now re-reading it I think I missed making it.

Thanks for the clarification.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Luke L (2/5/2010)
what I would do is create a stored procedure...

Heh... sorry Luke. I missed what you said above. Good to have a backup, though. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

no apologies necessary Jeff. Like I said once I re-read my comment, I realized I didn't make the point as strongly as a should have. Thanks for the help.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]