0

So this might be answered and I'd be happy if anyone could link it to me but I just didnt know how to search for this problem!

var getData = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName = '" + exerVariName + "' AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";

this is my line that gets the data from the database, I have a dropdown textbox thing that gives the exerVariName so whatever I choose in the dropdown gets inserted in this line ofc. 

<select name="exerVariName" >
    <option value="*">All</option>
    @foreach (var get in db.Query(getVariName)) {
        <option value="@get.exerVariName">@get.exerVariName</option> 
    }
</select>

So as you can see, I get the options to the dropdown from a database and put them out with the foreach, and I thought if I add a seperate one and put * as the value which I thought ment 'all' in the sql world. It didnt work, so I want to know what to put as the value in the first option the make the database select all in that column!

Pontus Svedberg
  • 305
  • 1
  • 7
  • 24
  • 3
    Not an answer, but: Please google SQL Injection and then use parameterized SQL in your code. This type of code is very vulnerable to SQL Injection and parameterized SQL is an easy cure. – driis Nov 28 '16 at 15:12
  • In general, you should not be using string concatenation with a query as it can leave you open to sql injection. It is best to use bind variables. @driis beat me to it. – Jacobr365 Nov 28 '16 at 15:12
  • Not that its an issue since this will just be a private page for myself and not be online in any way. But still, thank you, I have been wanting to learn about how to do that! – Pontus Svedberg Nov 28 '16 at 15:13
  • You will be glad to learn it now so the next time you do make a page that isn't private you won't be tempted to do it the wrong way! Bad habits can be hard to break. – Sam Marion Nov 28 '16 at 15:15
  • * is not a wildcard character, * typically tells the database engine that you want all of the columns. When you do `SELECT * FROM myTable`, you're saying give me all the columns in the table. If you wanted only certain values, you'd have to use wildcards. In SQL Server, you'd do something like `SELECT * FROM myTable WHERE name LIKE '%'` – Patrick Tucci Nov 28 '16 at 15:16
  • @PatrickTucci or simply drop the whole where clause.. – Steve Nov 28 '16 at 15:18
  • @Steve That's an option, but then the ability to filter would be gone. OP would have to add logic to use a different query string when filtering is required. Additionally, OP would have to have a different query string for each of the possible filtering combinations. What if the user only wanted to filter for exercise? Or exerVariName? Or date? Without wildcard substitution OP would need a query string for each possible permutation of filtering that they could possibly want. I can see pros and cons to doing it either way. – Patrick Tucci Nov 28 '16 at 15:25
  • I see, this makes sense! Thanks for the comments! Ill look in the wildcards and that and see what I can come up with! – Pontus Svedberg Nov 28 '16 at 16:30
  • SELECT * FROM Customers WHERE City LIKE '[a-z]%'; found this on w3schools, I could try it there and that would solve my problems, but im not sure how to implement it and still have my variable left, should I do some kind of IF and make exerVariName = "like'[a-z] if I select all, else just make exerVariName be what I selected ? – Pontus Svedberg Nov 28 '16 at 18:33

1 Answers1

0

You'll need to use Javascript to do this (jQuery might be the easiest one) or handle this part in you backend side, this means that if "exerVariName" has a value of "" it means that all options were "selected" ... I don't recommend saving all options because when you add a new value in the database to that list, you'll need to update previous data where "" was selected.

I can keep helping/guiding you if needed :)

Christian Melendez
  • 376
  • 1
  • 10
  • I see! if jQuery is the easiest ill go with that if you can help! Im just really looking for a way that works, no need to be the absolut best solution! :) – Pontus Svedberg Nov 28 '16 at 16:27
  • Sure thing, if you want to go with this path, you might not need to have an option to select all, maybe have a link or button that says "Select All" and add an event to that element that will select all options, take a look to this question: http://stackoverflow.com/questions/9924666/jquery-select-all-options-of-multi-select-select-box-on-click – Christian Melendez Nov 28 '16 at 16:56
  • bug again, that will depend on the data you want to save, do you want to save all options if the user wants to "select all"? If yes, this is the way to go if no, you just need to save a default value (0 or null) and this will say "ok, the user selected all options" – Christian Melendez Nov 28 '16 at 16:58
  • Couldnt make that work in the link! Copied the stuff from the jsfiddle demo and fixed all ids to right name and stuff, but I dont think anything happened! But to your second questing, nothing is being saved, its just to pull some date or in this case alla that data from the database to display on one page! Really nothing more and nothing less! – Pontus Svedberg Nov 28 '16 at 18:24