How to use quotes in ASP and SQL syntax

Accept a concept first: only double quotes are recognized in ASP, only single quotes are recognized in Access SQL, and HTML is recognized as a single double quote because of its lack of rigor. The above is a summary of my experience, the final correctness has yet to be confirmed.





in ASP, to output a double quote, you need to use the escape character: two double quotes ("").


For example,


to output the string ABC, Response.Write ("abc")


to output the string "ABC, The Response.Write (" "ABC")///double quotation marks are enclosed, indicating that the interior is a string. Finally, the remaining two double quotes, escape output is a double quotation mark.


to output string ab "C", then Response.Write ("AB" "C")


to output a double quote ", Response.Write (" "")//That explains why you write four double quotes


In addition, there is another way to use the acsii character


For example,


To output AB "C", then Response.Write ("AB" & Chr () & "C")








Next, let's look at the issue of single quotes in SQL. We consider this problem mainly to allow ourselves to do the string database processing without error, and prevent SQL injection.


to take a look at the simplest SQL injection, there is a message board whose form has a name item.


on the target page, you have the following code:


<%


name = Request.Form ("name")


Conn.execute Insert into guestbook (name) VALUES (' & name & ') '


%>


If we submit a name of Jacky, the above SQL statement is Insert into guestbook (name) VALUES (' Jacky '), which is obviously in line with our intent.


However, if we submit an I ' M Jacky, the above statement becomes Insert into guestbook (name) VALUES (' I ' m Jacky '), and then, unfortunately, the first single quote that the system finds is the single quote in I ' m, and the system The string (including quotes) that the user wants to commit is just ' I '. The next m Jacky ' system can't be explained, so you think your syntax is wrong.


How to solve it? That is, before you do database processing, replace one single quotation mark with two single quotes, and let the system interpret it as an escape character, as follows:


<%


name = Request.Form ("name")


name = replace (name, "'", "")//That is, name = replace (name, Chr (), Chr () &CHR (39))


Conn.execute Insert into guestbook (name) VALUES (' & name & ') '


%>


Resubmit  i ' m jacky, sql statement into  Insert Into GuestBook  (name)  VALUES  (' I ' m  jacky '),  when writing data, SQL automatically recognizes two single quote escape characters, which ultimately writes the data to the database as  i ' m jacky&nbsp, which is the correct result we expect.