SQL update problem

[Patrick D.]

C#, webapplication

Hi

usually this sql update command works fine:
(html is a memo-field)

UPDATE [Table1] SET

='content of a memo-field' WHERE [ContentID]='1'

As soon as I have a content like

'winter' is colder than "summer"

to assign to the html-field the sql-statement produces an error since the
little signs around winter and summer are a problem for the sql-statement.

Server.HtmlEncode(String) didn't help.

What do I need to do, to be able to assign any text-contents to the
memo-field?

Thanks for your help.

Patrick

 

[Göran Andersson]

C#, webapplication

Hi

usually this sql update command works fine:
(html is a memo-field)

UPDATE [Table1] SET

='content of a memo-field' WHERE [ContentID]='1'

As soon as I have a content like

'winter' is colder than "summer"

to assign to the html-field the sql-statement produces an error since the
little signs around winter and summer are a problem for the sql-statement.

Server.HtmlEncode(String) didn't help.

What do I need to do, to be able to assign any text-contents to the
memo-field?

Thanks for your help.

Patrick
[/QUOTE]

The preferrable way to handle this is to use a parameterised query. Then
you don't have to worry about encoding the string.

UPDATE [Table1] SET [html]=? WHERE [ContentID]='1'

Add a parameter to the command object. Example:

cmd.Parameters.Add("", OleDbType.VarChar, 0).Value = "'winter' is colder
than \"summer\"";

If you can't use parameters, you have to encode the string when you put
it in the query. This is done differently depending on what database you
are using. Here are some of the most common:

SQL Server: replace ' with ''
MS Access: replace ' with ''
MySQL: replace \ with \\, then replace ' with \'

 

[Patrick D.]

Hi Göran

Thanks for your input.

I now resolved the problem with string.Replace(OldChar, NewChar) while I
take a character, which is used very seldom to replace the " ' " quote.

Patrick

C#, webapplication

Hi

usually this sql update command works fine:
(html is a memo-field)

UPDATE [Table1] SET

='content of a memo-field' WHERE [ContentID]='1'

As soon as I have a content like

'winter' is colder than "summer"

to assign to the html-field the sql-statement produces an error since the
little signs around winter and summer are a problem for the sql-statement.

Server.HtmlEncode(String) didn't help.

What do I need to do, to be able to assign any text-contents to the
memo-field?

Thanks for your help.

Patrick
[/QUOTE]

The preferrable way to handle this is to use a parameterised query. Then
you don't have to worry about encoding the string.

UPDATE [Table1] SET [html]=? WHERE [ContentID]='1'

Add a parameter to the command object. Example:

cmd.Parameters.Add("", OleDbType.VarChar, 0).Value = "'winter' is colder
than \"summer\"";

If you can't use parameters, you have to encode the string when you put
it in the query. This is done differently depending on what database you
are using. Here are some of the most common:

SQL Server: replace ' with ''
MS Access: replace ' with ''
MySQL: replace \ with \\, then replace ' with \'
[/QUOTE]

 

[Göran Andersson]

Hi Göran

Thanks for your input.

I now resolved the problem with string.Replace(OldChar, NewChar) while I
take a character, which is used very seldom to replace the " ' " quote.

Patrick

That's a kludge. You should encode the string properly so that it's
stored correctly in the database.

 

[Jay R. Wren]

That's a kludge. You should encode the string properly so that it's stored
correctly in the database.

Patrick,

If this "memo field" is from a web page, you are vulnerable to SQL Injection
attack.

Please read these article and use the SQL Parameter api which Goran
suggestioned.

http://en.wikipedia.org/wiki/SQL_injection

http://msdn.microsoft.com/en-us/library/ms161953.aspx