-1

I have been using this sql statement for quite some time. Just noticed it only works on single words.

sql = "SELECT Count(*) as Totalab FROM tablename WHERE city = '" & Server.URLEncode(rs("city")) & "'"

For example:

For the city of Brampton this works fine and returns 32 (just example), because Brampton is 1 word. For the city of New York it returns 0 (where it should return 56, for example) because New York is 2 words.

Mark M
  • 1
  • 3
    What database you are working with ? – Valli Oct 27 '17 at 23:45
  • https://stackoverflow.com/questions/561954/asp-net-urlencode-ampersand-for-use-in-query-string – Valli Oct 27 '17 at 23:51
  • Shouldn't you be using `OR` rather than `AND`? – Alan Oct 27 '17 at 23:58
  • how does your query with two words work? It should probaby use an OR or IN e.g WHERE city IN ('brampton', 'new york') – Kevin Oct 27 '17 at 23:59
  • Use parameters instead of munging queries with such values. – Gordon Linoff Oct 28 '17 at 00:34
  • Gordon, could you point me in right direction – Mark M Oct 28 '17 at 01:31
  • Alan, there is no OR or AND – Mark M Oct 28 '17 at 01:31
  • Valli, thank you, but that's not it. – Mark M Oct 28 '17 at 01:32
  • Without sample data, and results related to that sample, AND which dbms this is for, I can't see you getting an answer. Why would you get 56 for "New York"? What about "Wotton-under-Edge" or similar? [for more, see](https://english.stackexchange.com/questions/207787/united-kingdoms-three-name-cities-is-there-a-generic-way-to-write-them) – Paul Maxwell Oct 28 '17 at 03:41
  • 1
    You're asking for SQL Injection attacks: https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work – Greg Oct 30 '17 at 22:20

2 Answers2

0

Are you trying to identify the matching cities? If so, why not try using WHERE EXISTS conditions? 

SELECT Totalab
FROM table
WHERE EXISTS (SELECT City FROM Table WHERE CityId = 
SomeValue AND State = XX);
Adrian Russo
  • 1
  • 2
0

Assuming this is VB, that's because you're not querying the database with New York.

You're actually querying it with New+York.

That's because, for whatever strange reason, you're URL Encoding the string, which is replacing the space () with a plus (+).

If, for whatever reason, you're dealing with encoded parameters, you should be decoding them, although presumably whatever framework you're using will already be doing that for you anyways.

Really, though, I'd be far more concerned about SQL Injection, which is a likely problem if you're simply concatenating the strings. You need to be using prepared statements, which might also solve whatever problem you think you're dealing with here.

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45