1

I am working in Classic ASP. I know there is a record that matches my simple SQL select query. It has the ' character ' in it. The code is as follows:

Fieldname = Replace(trim(Request.form("Fieldname")),"'","'", 1, 10)  
'replace the "'" up to 10 times with the ' code to avoid SQL issues, LOL.

SQL = "select id,fieldname from table where fieldname='"&Trim(Fieldname)&"'"

set rs = server.createobject("adodb.recordset")
rs.open SQL, Application("conn"), 1, 1
If not rs.eof then
    response.redirect "somepage.asp?QS=Fieldname_Exists_in_DB"
Else
    'Sample.hold the value in a hidden input field and pass it to the next page
End If

The problem is, I know for a fact the fieldname and fieldname value is in the MS-SQL 2016 server table. I pull data from it all the time. The value in the database field contains the ' value as does the Replaced FORM Fieldname when it is compared to the SQL database field, so it should NOT pass the IF NOT RS.EOF question. Yet it passes every time.

What am I missing? I'm doing the exact same query in other places on this exact same app and it behaves as one would expect.

user692942
  • 16,398
  • 7
  • 76
  • 175
Rusty Old Man
  • 13
  • 3
  • Can you plase add some sample data with your expected output from them? – mkRabbani Nov 18 '19 at 06:25
  • I did already. The sample data if the record exists would send the user to an error page denoted as "somepage.asp?QS=Fieldname_Exists_in_DB" and if the record did not exist it would hold the form field value in a hidden input html form field and pass it to the next page. This is a step by step form to an ultimate conclusion. Thank you for asking. – Rusty Old Man Nov 18 '19 at 06:39
  • 2
    All of the circumventing you are doing to avoid the use of apostrophes in the field name is absolutely unnecessary. If you use a parameterised query you avoid both this issue and the fact you’re code is wide open to SQL Injection. Revise the code to use a `ADODB.Command` object to execute a parameterised query. You can do the exact same query using the `CommandType = adCmdText` setting the `CommandText` to `"select id,fieldname from table where fieldname=?"`. See [SQL insert into database with apostrophe](https://stackoverflow.com/a/22037613/692942) *(process is the same for a `SELECT` query)* – user692942 Nov 18 '19 at 06:58
  • I am not inserting anything. I am looking to see if something exists, hence the 1, 1 after the application level connection. I'd of used a 1,3 if I were modifying the database. I appreciate your help, but I do not use the long words to equate to a 1, 1, when typing 1, 1 is so much quicker. I do not see any injection vulnerability, but like my name says, I am old and rusty. BUT, for the sake of argument, I like to remove special characters as a precaution to any errors as they do seem to cause a lot of errors in vbscript, which is all I really know. – Rusty Old Man Nov 18 '19 at 07:24
  • @RustyOldMan if you read my comment fully you would see the disclaimer that the technique is the same when using a `SELECT` statement, don't focus on the `INSERT` statement. As for using “magic numbers” instead of Named Constants that is up to yourself but makes for far less readable code. – user692942 Nov 18 '19 at 07:45
  • @RustyOldMan here’s a [simpler example](https://stackoverflow.com/a/20470431/692942). – user692942 Nov 18 '19 at 07:50
  • The way this SELECT statement is supposed to work is it takes Request Form input and then uses it as the WHERE, but NOT with an ID as I am looking in a nchar field for a match to the Form value. I do not have an ID to work with, nor do I need one. I need to ensure that whatever the value is in the submitted Form does not match any of the records in the table. If it does not match any records in the table then I am good to go to the next page of code, otherwise I redirect the user to an error telling them to input a different value. – Rusty Old Man Nov 18 '19 at 19:53
  • 1
    @RustyOldMan What are you on about? I'm trying to help you, using a parameterised query will allow you to do that without the risk of SQL Injection and without having to manipulate the form field value because it contains apostrophes. You're taking the examples given to literally, the field doesn't have to be `id`. I already provided you the SQL to use in [a previous comment](https://stackoverflow.com/questions/58908649/select-with-character-39-in-it#comment104081001_58908649), just apply that to [the example method](https://stackoverflow.com/a/20470431/692942). – user692942 Nov 19 '19 at 06:03
  • Your example is over my rusty old head I guess. Please accept my thanks for trying. I do appreciate your effort. – Rusty Old Man Nov 19 '19 at 09:01
  • 1
    @Lankymart is correct (as usual). Parameterize your queries. Beyond fixing these kinds of issues, it's a huge boost to security – Stephen R Dec 03 '19 at 19:06

1 Answers1

3

Tried to explain in the comments but as the point is being missed, I'll try to give you an example here.

Do not trust user input

Classic ASP server-side code that interacts with the ADODB Library doesn't have any notion of sanitised data. This means that any input that comes from the user via the Request object (like Request.Form("Fieldname")) should not be trusted.

Fieldname = Replace(trim(Request.form("Fieldname")),"'","'", 1, 10)
SQL = "select id,fieldname from table where fieldname='"&Trim(Fieldname)&"'"

This example is open to SQL Injection attacks and is generally bad practise and leads to security flaws that can be easily exploited with script tools readily available on the internet.

Manually sanitising data

Apart from the security flaws introduced, it also makes it harder to query data due to how SQL calls for strings and other data types need to be constructed (which varies from provider to provider). Having to account for the various combinations of characters that could be deemed dangerous or likely to break the query can be a cumbersome task and one seen far too often in the wild when ADODB already has a solution.

Parameterised Queries

The ADODB Library has an in-built object called ADODB.Command which takes all these hassles away.

Using the example in the question the same query can be written without the failings of manually sanitising data or executing SQL directly against user input.

Const adCmdText = 1
Const adVarWChar = 202
Const adParamInput = 1

Dim Fieldname, SQL, cmd, rs, 
Fieldname = Trim(Request.Form("Fieldname") & "")

SQL = "SELECT id, fieldname FROM table WHERE fieldname = ?"

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
  .ActiveConnection = Application("conn")
  .CommandType = adCmdText 'Also can use 1
  .CommandText = SQL
  Call .Append(.CreateParameter("@fieldName", adVarWChar, adParamInput, 255))
  Set rs = .Execute(, Array(Fieldname))
End With
Set cmd = Nothing

If Not rs.EOF then
    response.redirect "somepage.asp?QS=Fieldname_Exists_in_DB"
Else
    'Sample.hold the value in a hidden input field and pass it to the next page
End If

Useful Links

user692942
  • 16,398
  • 7
  • 76
  • 175