SQL replace

Hi,

I am trying to replace "<" and ">" in SQL so the string can be inserted without any issues. This is what I do

update table
set note = replace(@Notes, '<', '')
where id =2

Open in new window


The code works if the "<" stands alone.
For example, "This is a test < 123."  ===> "This is a test 123"

But it does not work when "<" is "attach" to other text.
For example, "This is a test <test@test.com."

Any ideas? thanks
mcrmgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
it works for me!

DECLARE @Notes VARCHAR(100) = 'This is a test <test@test.com.'
DECLARE @Notes2 VARCHAR(100) = 'This is a test < 123.'

SELECT @Notes, REPLACE(@Notes, '<', '')
SELECT @Notes2, REPLACE(@Notes2, '<', '')

Open in new window


One question for you: where do you get @Notes from?
0
mcrmgAuthor Commented:
I found out SQL is not causing the issue.  The error is from .NET

Whenever I use < or >, I get the error

Exception Details: System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (Notes="...fer rfer  <wedwedwed@abc.com ").
0
Éric MoreauSenior .Net ConsultantCommented:
very different issue!

could it be that you need to use UrlEncode? https://msdn.microsoft.com/en-us/library/system.net.webutility.urlencode(v=vs.110).aspx
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

mcrmgAuthor Commented:
Sorry about that.

What I am doing is to capture whatever users type in and save it to the database.

Users might copy the email and post it into the textbox. So it could have My Name <MyName@cmpany.com> in the text.

        String StringNotes = Notes.Text.ToString();

        StringNotes = StringNotes.Replace("<", "");
        StringNotes = StringNotes.Replace(">", "");

      
        SqlCom.Parameters.Add("@Notes", System.Data.SqlDbType.VarChar, 8000).Value = StringNotes;

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
you will have to provide us more details (because we don't have access to nay of your code!).

Is it possible that you error occurs elsewhere? You might have to debug your app to find out exactly where it happens.
0
mcrmgAuthor Commented:
this is the code
        SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString());



        con.Open();
        SqlCommand SqlCom = con.CreateCommand();
        SqlCom.CommandText = "Execute st_test @ID, @Notes, @UserUpdate";

        

        String StringNotes = Notes.Text.ToString();


        StringNotes = StringNotes.Replace("<", "");
        StringNotes = StringNotes.Replace(">", "");

        
        SqlCom.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = Convert.ToInt32(Request.QueryString["CMNoteID"]);
        SqlCom.Parameters.Add("@Notes", System.Data.SqlDbType.VarChar, 8000).Value = StringNotes;
        SqlCom.Parameters.Add("@UserUpdate", System.Data.SqlDbType.VarChar, 50).Value = Session["user"];

Open in new window




            
the text could be like this for the NOTE: (copied from the email received)

From: test user [mailto:test@test.net]
Sent: Monday, April 09, 2018 2:57 PM
To: Someone
Subject: Re: Asset Management

Hi,

How are you doing?


Sent from my iPhone

On Apr 9, 2018, at 2:34 PM, user 1 <user1@abc.com> wrote:
ok – Good day! Hope all is well!




As you can see the < and > is causing the problem.  thanks
0
Éric MoreauSenior .Net ConsultantCommented:
As said before, is it possible that you error occurs elsewhere? You might have to debug your app to find out exactly where it happens.

I suspect that you don't even get to that SQL Command.
0
mcrmgAuthor Commented:
if I remove < and >, the data can be saved to SQL....strange  thanks
0
Éric MoreauSenior .Net ConsultantCommented:
could it be the data travelling from your page to your code before reaching the SQL Command. It looks like your app is an ASP.Net application and in that context < and > have special meaning and you need to encode them.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Exception Details: System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client...

how is your form doing the posting to Code Behind?

if i remember correctly, we need to set some configuration (in Web.Config if not wrong) to a lower version of .NET Framework in order to bypass that error OR we need to find alternative solutions
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what i mean is adding:

<httpRuntime requestValidationMode="2.0" />

Open in new window


into your web config.

for more info, check this out:

A potentially dangerous Request.Form value was detected from the client
https://stackoverflow.com/questions/81991/a-potentially-dangerous-request-form-value-was-detected-from-the-client
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Sorry as what previously suggested probably won't sufficient. Try add this in web.config instead...

<system.web>
    <httpRuntime requestValidationMode="2.0" />
    <pages validateRequest="false" />
</system.web>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcrmgAuthor Commented:
Thank you very much, It is working now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.