0

My website sells garden plants so there are a lot of apostrophes in the names, i.e Canna 'Taney'. When I list the plants in a certain category, or return search results, those with an apostrophe get listed at the top and those without at the bottom, like this :

Canna 'Aphrodite'

Canna 'Brilliant'

Canna 'Carnival'

Canna 'Cleopatra'

Canna 'En Avant'

Canna Australia

Canna Bird of Paradise

Ideally Canna Australia should be second in the list and Canna Bird of Paradise third. I assume getting the the "order by" to ignore the apostrophes is the answer, can it be done?

set rscatlist=db.execute("select * from products where catcode=" & catcode & " order by name ASC")

Is the code that needs modification.

user692942
  • 16,398
  • 7
  • 76
  • 175
Malcolm Browne
  • 11
  • 3
  • possible duplicate of [How can I make a prepared statement in classic asp that prevents sql injection?](http://stackoverflow.com/questions/8538979/how-can-i-make-a-prepared-statement-in-classic-asp-that-prevents-sql-injection) – Alex Feb 26 '15 at 21:17
  • @Alex Although they do indeed have an issue with SQL Injection that isn't the question the OP is asking. – user692942 Feb 27 '15 at 13:37

1 Answers1

2

You can remove any apostrophe in "order by" condition:

select * from products where catcode=" & catcode & " order by REPLACE(name,'\'','') ASC
cSteusloff
  • 2,487
  • 7
  • 30
  • 51
  • Would have preferred you had at least pointed out how open to SQL Injection writing SQL in Classic ASP like this is. Where does `catcode` come from, if it's pasted by a form or through the URL then the OP needs to consider re-writing this using `ADODB.Command`. – user692942 Feb 27 '15 at 13:39
  • REPLACE(name,'\'','') sorted it out and I'm grateful for that answer. @Lankymart, I know nothing about SQL injection but if there is a better way to resolve this I'd like to be pointed in the right direction. catcode is being passed via the URL. – Malcolm Browne Feb 27 '15 at 17:52
  • @MalcolmBrowne If you know nothing about SQL Injection and are building web content suggest you learn fast. It is where using crafted exploits on the URL querystring inputs added directly into SQL queries can open up more access to the database then you first anticipated, from changing content to completely deleting it. Take a look at the [linked question](http://stackoverflow.com/questions/8538979/how-can-i-make-a-prepared-statement-in-classic-asp-that-prevents-sql-injection?lq=1) to learn more. Also good explanation of what [SQL Injection is here](http://en.wikipedia.org/wiki/SQL_injection). – user692942 Feb 28 '15 at 09:46