Sql insert Question

Discussion in 'ASP .Net' started by =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Nov 18, 2004.

  1. 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
     
    =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Nov 18, 2004
    #1
    1. Advertising

  2. 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
     
    =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Nov 19, 2004
    #2
    1. Advertising

  3. =?Utf-8?B?UGF0cmljay5PLklnZQ==?=

    John M Deal Guest

    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
     
    John M Deal, Nov 19, 2004
    #3
  4. 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" <> wrote in message
    news:...
    > 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
     
    Kevin Spencer, Nov 19, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Harry Zoroc
    Replies:
    1
    Views:
    946
    Gregory Vaughan
    Jul 12, 2004
  2. cannontrodder
    Replies:
    1
    Views:
    734
    cannontrodder
    Jul 25, 2006
  3. Replies:
    1
    Views:
    454
  4. ecoolone
    Replies:
    0
    Views:
    763
    ecoolone
    Jan 3, 2008
  5. Replies:
    1
    Views:
    1,788
    Albert Hopkins
    Dec 6, 2008
Loading...

Share This Page