Variables in SQL Query not working

  CutNpaste 16:52 03 Apr 08
Locked

Hi i need help with ASP VBSCRIPT. I have the following variables:

Dim GroupID
Dim order

Staff_code=Server.HtmlEncode(Request("Staff_code"))

order=Server.HtmlEncode(Request("order"))

and the following SQL Statement with these variables inside it:

strSQL = "SELECT DISTINCT Student_ID, PHOTO, surname, forename, GroupID, Staff_code FROM aSelAllStudentByClass WHERE GroupID='" & GroupID & "' ORDER BY '" & order & "';"

As long i dont have the order variable inside the query then the script works but as soon as I add it in the query I get the following error:

'Microsoft OLE DB Provider for SQL Server error '80040e14'

A constant expression was encountered in the ORDER BY list, position 1.'

My variable for order= Student_ID ASC and should technically work.

Can anyone help please!! thanks

  beynac 17:22 03 Apr 08

Try removing the single quotes around your *order* variable in the query (i.e. & "ORDER BY " & order & ";")

  CodeMeister 22:00 03 Apr 08

You've left yourself wide open to SQL injection attacks which will allow someone to delete all of your data or run their own SQL code.

click here for an article about SQL injection attacks and how to prevent them.

  CutNpaste 09:57 04 Apr 08

Hi beynac,
removing the single quotes worked. I still dont understand why the other varialbe works with the single quotes and this one doesnt.

thanks for that!!

  CutNpaste 09:59 04 Apr 08

Thanks for the security issue. Iam aware of the risks. I wll deal will security later. Its not such a big issue here as the site is hosted internally and the most genius Hackers would be Teachers who dont know anything about computers lol.

thank for the link i will check it out!!

  beynac 11:49 04 Apr 08

The other variable needs the quotes as it is selecting the value of the variable 'GroupID' from the database field named GroupID. It's a bit confusing as the database field name is the same as the name of the variable. It is best to make sure that they are different. For example, if you were to rename the variable as 'InputGroup' and set a value for this as 'ABC' your query (strSQL) would read:

strSQL = "SELECT DISTINCT Student_ID, PHOTO, surname, forename, GroupID, Staff_code FROM aSelAllStudentByClass WHERE GroupID='" & InputGroup & "' ORDER BY " & order & ";"

This would give an SQL query of:

SELECT DISTINCT Student_ID, PHOTO, surname, forename, GroupID, Staff_code FROM aSelAllStudentByClass WHERE GroupID='ABC' ORDER BY Student_ID ASC;

Note the quotes around the value 'ABC' and none around the field name Student_ID.

Hope this helps.

  CutNpaste 12:37 04 Apr 08

oh i get it now. that was a very good way of explaining. thanks very much!!

  beynac 17:21 04 Apr 08

You're welcome. :)

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

New Corel ParticleShop plugin now available: 11 new brushes & 6 new brush packs

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…