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?
 
Ryan ChongConnect With a Mentor Commented:
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
 
É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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
É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
 
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 ChongCommented:
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 ChongCommented:
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
 
mcrmgAuthor Commented:
Thank you very much, It is working now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.