Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

sql query not recognizing appostrophe

the code in attached snippet works fine, except  for one conditon when company name  contains an appostrophe. "irene's  abc".
it is not updating the field with respect to this information.how to do tihs in an sql query
Try
            If Not ddlAccounts.SelectedItem.Text = "-- Velg konto --" Then
                Dim sql As String = "UPDATE Companies SET  IsShow= 0 WHERE CompanyName= '" & ddlAccounts.SelectedItem.Text & "'"
                Dbc.ExecReader(sql, PageDbConn)
            End If
        Catch ex As Exception

        End Try

Open in new window

0
Ammar Iqbal
Asked:
Ammar Iqbal
  • 6
  • 4
  • 2
  • +3
2 Solutions
 
TempDBACommented:
Its because when you give company name with apostrophe, the following line which you are building dynamically messes up.

Dim sql As String = "UPDATE Companies SET  IsShow= 0 WHERE CompanyName= '" & ddlAccounts.SelectedItem.Text & "'"

So, you need to check before using ddlAccounts.SelectedItem.Text value whether it contains an apostrophe. If yes, you need to take proper care with escape characters.SQL server will think that its end of the string when it gets an apostrophe.
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
yes i understand this. an you give me an example how to take propercare  fro example  if this  comes
Dim sql As String = "UPDATE Companies SET  IsShow= 0 WHERE CompanyName= '" & 'Irene's selskap' & "'"

0
 
Ramkisan JagtapLead DeveloperCommented:
change the following
ddlAccounts.SelectedIndex.Text.Replace("'","''");
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pritamduttCommented:
Do something like this
1. Copy the Selected Item Text into a String
2. Replace Single quote with two single quotes

String strInput = ddlAccounts.SelectedItem.Text

Dim sql As String = "UPDATE Companies SET  IsShow= 0 WHERE CompanyName= '" & strInput.Replace("'", "''") & "'"

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
but will this work for string which does not contain any appostrope.  we don't know anything can come.
0
 
TempDBACommented:
Sorry, went offline for a while. I second Pritamdutt answer and it will work with both apostrophe and without apostrophe.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Please my friends use parameters!, is the correct way to do this:
Try
    If Not ddlAccounts.SelectedItem.Text = "-- Velg konto --" Then
        Dim myCommand As New System.Data.SqlClient.SqlCommand("UPDATE Companies SET IsShow = 0 WHERE CompanyName = @pCompanyName", PageDbConn)
        myCommand.Parameters.Add("pCompanyName", System.Data.SqlDbType.VarChar, 100).Value = ddlAccounts.SelectedItem.Text
        ' YOU MUST CHANGE YOUR ExecReader METHOD TO ACCEPT A SqlCommand CLASS INSTEAD OF A STRING
        Dbc.ExecReader(myCommand, PageDbConn)
    End If
Catch ex As Exception

End Try

Open in new window

0
 
Anthony PerkinsCommented:
Or simply use Stored Procedures and you don't have to worry about single quotes.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
@acperkins, to
don't have to worry about single quotes
you still need parameters (assuming that SpMyTest has defined the @pCompanyName varchar(100) parameter):

Dim myCommand As New System.Data.SqlClient.SqlCommand("dbo.SpMyTest", PageDbConn)
myCommand.CommandType = System.Data.CommandType.StoredProcedure
myCommand.Parameters.Add("pCompanyName", System.Data.SqlDbType.VarChar, 100).Value = ddlAccounts.SelectedItem.Text

Open in new window


I don't want to sound bad but otherwise you will fall in the same problem, always is a good practice to use a command and parameters when you do a DB query that need it, its a good for Sql Server (execution plans) and you, you don't need to worry about sql injections attacks, all that stuff is handled by the ado net classes.
0
 
Anthony PerkinsCommented:
I was making the assumption that if they did it the right way by using Stored Procedures, they would of course use parameters.

Incidentally the only way you are going to re-use Sql Server Plans is if you use Stored Procedures.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
@acperkins
Incidentally the only way you are going to re-use Sql Server Plans is if you use Stored Procedures.
Buddy, the SqlCommand always use sp_executesql to execute your commands, whatever it is, a simple query or a sp, so it always take advantage of execution plans.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
You can use Sql Server Profiler to confirm this.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
And I dont want to sound annoying, but about:
I was making the assumption that if they did it the right way by using Stored Procedures, they would of course use parameters.
Well, I think the opposed, if a guy is used to concatenate text to execute his query's and you say him to use a SP I'm sure he is going to do this if you don't full explain how to call the SP:
' Yeepee now i'm using SP, all will be fine!!
Dim myCommand As New System.Data.SqlClient.SqlCommand("EXEC dbo.SpMyTest @pCompanyName = '" + ddlAccounts.SelectedItem.Text + "'", PageDbConn)

Open in new window

0
 
Anthony PerkinsCommented:
>>Buddy, the SqlCommand always use sp_executesql to execute your commands, whatever it is, a simple query or a sp, so it always take advantage of execution plans.<<
My name is not "Buddy" (most people here call me by my name: Anthony) and I am afraid you are not only not correct, but we are talking about different animals.  Even when sp_executesql is used it has to compile the SQL statement.  Also, while it is likely that it will reuse the execution plan, there is no guarantee.  Finally, there is the giant 800 pound gorilla in the room called Security.  In order to execute any SQL statement without using Stored Procedures it has to have explicit permissions on the tables/views/UDFs used.  Not so with Stored Procedures, that is provided the Stored Procedures do not use Dynamic SQL, and then you would be no better off.

But I suspect we are going severely off-topic...
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
@Anthony
1. Was not my intention to make you upset.
2. You say: Incidentally the only way you are going to re-use Sql Server Plans, because of that I only want to make it clear that when you use SqlCommand to execute your query that is not the case, and you are right that sp_executesql cant guarantee that it will reuse the execution plan .
3. I always assume that the sql server user that execute the command has dbo rights over the DB, too many people don't take care of this.
4. If somebody use concatenation to build the query, for example, to execute a stored procedure, there is still the risk of sql injection attack (see the last example).
5. My apology if I offended you in some way, English is not my primary language.
0
 
Anthony PerkinsCommented:
1. Not at all.  I realized after the fact that you joined EE recently.
2. Fair enough.
3. Most shops would not allow a user that executes any SQL command to belong to the db_owner role, that would be a giant red audit flag.  Usually best practices indicate that the user have no permissions, other than EXECUTE permissions on Stored Procedures.
4. Absolutely.  Clearly not a good idea.
5. It is not mine, either and no offense taken.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now