I have problem with sql query with aspnet.
In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark =>' .
Any suggestions? Thanks.
Use a parameterized query instead of building a SQL String to execute. If you must continue building a SQL string, then you need to replace all the quotes in any string field with two quotes.
Example:
strSQL="SELECT * FROM table WHERE field1='" & textbox1.text & "'"
You can change that to:
strSQL="SELECT * FROM table WHERE field1='" & textbox1.text.replace("'","''") & "'"
or:
dim conn as new sqlconnection(configurationmanager.connectionstrings("{your key here").ConnectionnString)
conn.open
dim cmd as new sqlcommand("SELECT * FROM table WHEREfield1=@.field1",conn)
cmd.parameters.add(new sqlparameter("@.field1",sqldbtype.varchar))
cmd.parameter("@.field1").value=textbox1.text
' Execute the following line if you want a datareader
dim dr as sqldatareader=cmd.executedatareader
' Execute the following 4 lines if you want a dataset
dim ds as new dataset
dim da as sqldataadapter=new sqldataadapter(cmd)
da.fill(ds)
conn.close
|||
a parameterized query is definately the way to go.
If you continue to use concatenated SQL statements, you are opening yourself up to sql injection attacks.
|||Another option is to use a Stored Procedure - this way you don't have to write an SQL statement at all in your web application. You also gain a performance benefit since the Procedure is already compiled in SQL Server.dim objCmd as sqlcommand
dim objRdr as sqldatareader
objCmd = new sqlcommand(spNameHere, dbConnObjectHere)
objCmd.commandType = commandtype.storedprocedure
objCmd.parameters.Add("@.SearchField", textBox1.text)
objRdr = objCmd.executeReader
When you use the parameters collection in this way there is no need to worry about single quotes, percent signs, or other characters messing with your query. You can add as many parameters as you need to.|||
rich freeman wrote:
You also gain a performance benefit since the Procedure is already compiled in SQL Server.
Beginning with SQL Server 2000, the execution plan of a parameterized sql statement is also cached just like with a stored proc. Therefore performance differences between stored procs and parameterized sql statements should not be used as a significant factor when choosing one technique over the other.
|||Thanks you everybody for the reply. All the replies are really informative (thumbs up!)! With the suggestions from you all, I have finally solved the problem. Thanks a lot!!!
