ASP site, MySQL database "order by" results

Question

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.

possible duplicate of How can I make a prepared statement in classic asp that prevents sql injection? — Alex
– Alex, Commented Feb 26, 2015 at 21:17
@Alex Although they do indeed have an issue with SQL Injection that isn't the question the OP is asking. — user692942
– user692942, Commented Feb 27, 2015 at 13:37

cSteusloff · Accepted Answer · 2015-02-26 21:25:07Z

2

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

select * from products where catcode=" & catcode & " order by REPLACE(name,'\'','') ASC

answered Feb 26, 2015 at 21:25

cSteusloff

2,6287 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user692942 Over a year ago

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.

Malcolm Browne Over a year ago

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.

user692942 Over a year ago

@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 to learn more. Also good explanation of what SQL Injection is here.

Collectives™ on Stack Overflow

ASP site, MySQL database "order by" results

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related