SQL statement not filtering rows

Question

The following code passes this row. The last column is named toscode. I am trying to weed out rows whose toscolumn contains "W1" anywhere in the name. No columns are set up as an index. Doesn't work.

I read that the $ (escape) in front of W1 is to prevent SQL injection. Not sure what the % do, but it might have to do with indexes which is maybe why it doesn't work.

| SPX    |   0 |    8.3 |    8.7 | 2017-03-24 |               0 |   30 | PUT |   2400 | SPXW170324C02400000 | .SPXW1703242400 |

MySqlCommand cmd = conn.CreateCommand();
cmd.Parameters.AddWithValue("@symbol", symbol);
cmd.CommandText = @"select * 
                    from contractdetails 
                    where symbol = @symbol 
                       && toscode NOT LIKE '%$W1%'
                       && Type = 'PUT' 
                       && oBid > .09 
                       && not ITM  
                       && Days > 10 
                       && Days < 120 
                    order by Days asc";

If you're trying to select only rows that contain "W1", you should use `LIKE`, not `NOT LIKE`. — shmosel, Feb 22 '17 at 23:44
@RiggsFolly *I am trying to **weed out** rows whose `toscolumn` does not contain "W1" anywhere in the name.* — shmosel, Feb 22 '17 at 23:46
@RiggsFolly If OP wants to **exclude** rows **without** "W1", then he wants to **include** only rows **with** "W1". Not sure how else to put it. — shmosel, Feb 22 '17 at 23:49
@RiggsFolly Either you're interpreting "weed out" as "select", or one of us has had too many drinks. — shmosel, Feb 22 '17 at 23:52
@shmosel I have only had one large _Rusty Nail_ :) How about you? — RiggsFolly, Feb 22 '17 at 23:53
@Ivan `%` is a wildcard which will match anything before and after "W1". I don't know what `$` is for, but you don't need to worry about SQL injection for your own hardcoded input. — shmosel, Feb 22 '17 at 23:55

Cam Bruce · Answer 1 · 2017-02-22T23:51:50.677

2

your LIKE clause should be:

toscode NOT LIKE '%W1%'

remove the '$' -it doesnt belong or have any meaning in a LIKE clause. You would only have to worry about SQL injection if it was user provided input, which in your example is not the case.

the '%' character is a wildcard, so anything could be before or after 'W1' in your search

edited Feb 22 '17 at 23:51

answered Feb 22 '17 at 23:44

Cam Bruce

5,412
14
30

That works, but what about "escaping to prevent SQL injection"? Not that I care that much... – Ivan Feb 22 '17 at 23:51
Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Feb 22 '17 at 23:52

SQL statement not filtering rows

1 Answers1