Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Sql insert Question

Reply
Thread Tools

Sql insert Question

 
 
=?Utf-8?B?UGF0cmljay5PLklnZQ==?=
Guest
Posts: n/a
 
      11-18-2004
Hi,
I have got this SQL below updating a textbox and a checkBox.

strSql = "Update Products Set Discontinued=" & chkBoxChecked & ",ProductName
= '" & ProductName & "' Where ProductID=" & ProductID

it outputs error :- Incorrect syntax near 's'. Unclosed quotation mark
before the character string ' Where ProductID=4'.

I can't see what is wrong can somebody just look through this..
Maybe tired
Thx
 
Reply With Quote
 
 
 
 
=?Utf-8?B?UGF0cmljay5PLklnZQ==?=
Guest
Posts: n/a
 
      11-19-2004
found my error i noticed i was inserting an apostrophe for example the word
(code's) in into the DB...
Whats the best way to replace this when inserting and editing and updating
this..
This problem come up especially when updating a field!!


"Patrick.O.Ige" wrote:

> Hi,
> I have got this SQL below updating a textbox and a checkBox.
>
> strSql = "Update Products Set Discontinued=" & chkBoxChecked & ",ProductName
> = '" & ProductName & "' Where ProductID=" & ProductID
>
> it outputs error :- Incorrect syntax near 's'. Unclosed quotation mark
> before the character string ' Where ProductID=4'.
>
> I can't see what is wrong can somebody just look through this..
> Maybe tired
> Thx

 
Reply With Quote
 
 
 
 
John M Deal
Guest
Posts: n/a
 
      11-19-2004
What you are seeing is a classic example of a vulnerability to a SQL
Injection attack. Obviously you want to fix this here, however you
really need to fix this wherever you have concatenated SQL statements or
you risk users (or potential hackers) really messing with your database
(I won't preach but PLEASE!!! go look up information on SQL Injection
and cross site scripting attacks).

To fix this there are four possibilities.

The best way to correct this is to migrate your sql statements into
parameterized stored procedure calls.

If your database doesn't support parameterized stored procedures or you
don't want to use stored procedures you should implement parameterized
queries. To do this you would structure your query like:

string sql = "Update Products Set Discontinued=@Discontinued,
ProductName=@ProductName Where ProductId=@ProductId";
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.Add("@Discontinued", SqlDbType.Bit).Value = chkBoxChecked;
cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 255).Value =
ProductName;
cmd.Parameters.Add("@ProductId", SqlDbType.Int).Value = ProductID;
cmd.ExecuteNonQuery();

This will fix the single quote issue. Not I realize it is in C# instead
of VB.Net but I think you'll translate it with out a problem. Also it is
setup for SQL Server but the concept should translate to whichever
database object type you are working with.

Third if your database supports it you can try to replace each single
quote with two single quotes (not double quotes but literally two
single quotes). To do this you could do a

strSql.Replace("'", "''")

Finally, and probably worst of all you could try to filter out invalid
characters but this could remove important data and/or miss things.

Hope this helps.

Have A Better One!

John M Deal, MCP
Necessity Software


Patrick.O.Ige wrote:
> found my error i noticed i was inserting an apostrophe for example the word
> (code's) in into the DB...
> Whats the best way to replace this when inserting and editing and updating
> this..
> This problem come up especially when updating a field!!
>
>
> "Patrick.O.Ige" wrote:
>
>
>>Hi,
>> I have got this SQL below updating a textbox and a checkBox.
>>
>>strSql = "Update Products Set Discontinued=" & chkBoxChecked & ",ProductName
>>= '" & ProductName & "' Where ProductID=" & ProductID
>>
>>it outputs error :- Incorrect syntax near 's'. Unclosed quotation mark
>>before the character string ' Where ProductID=4'.
>>
>>I can't see what is wrong can somebody just look through this..
>>Maybe tired
>>Thx

 
Reply With Quote
 
Kevin Spencer
Guest
Posts: n/a
 
      11-19-2004
In the SQL language, the single quote is a string delimiter, used to
identify the data type of tokens in the SQL string. You can escape it by
doubling it (use 2 single quotes together).

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Neither a follower
nor a lender be.

"Patrick.O.Ige" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> found my error i noticed i was inserting an apostrophe for example the

word
> (code's) in into the DB...
> Whats the best way to replace this when inserting and editing and updating
> this..
> This problem come up especially when updating a field!!
>
>
> "Patrick.O.Ige" wrote:
>
> > Hi,
> > I have got this SQL below updating a textbox and a checkBox.
> >
> > strSql = "Update Products Set Discontinued=" & chkBoxChecked &

",ProductName
> > = '" & ProductName & "' Where ProductID=" & ProductID
> >
> > it outputs error :- Incorrect syntax near 's'. Unclosed quotation mark
> > before the character string ' Where ProductID=4'.
> >
> > I can't see what is wrong can somebody just look through this..
> > Maybe tired
> > Thx



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Multiple Records Using One Insert Statemen with MySQLdb module anton.ranieri.it@gmail.com Python 1 12-06-2008 12:47 PM
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
insert in std::map does not insert the object simon.elbaz@free.fr C++ 1 11-04-2007 11:42 PM
Detailsview loses insert mode contents when Insert fails... cannontrodder ASP .Net 1 07-25-2006 08:38 AM
Tools to extract data from SQL database and convert it into XML & insert XML data into SQL databases Harry Zoroc XML 1 07-12-2004 10:10 PM



Advertisments