Options

Read Database, query sql in

WentingLi13WentingLi13 Member Posts: 9 Learner I
edited April 2020 in Help
My query sql is SELECT xxxxxx FROM table.name WHERE columnA in (?)
I want to define a macro which name %{list} to instead of ?
I've tried %{list} value is A, execute the query success, but when value is A,B, my query result is null, so i want to know, which separate charactor should i use when there is multi value in SQL IN(?)

Tagged:

Best Answers

  • Options
    rfuentealbarfuentealba Moderator, RapidMiner Certified Analyst, Member, University Professor Posts: 568 Unicorn
    edited April 2020 Solution Accepted
    Ahhh you are using parameters.

    It won't work, because parameters are meant to sanitize the SQL query you are creating. Just use this as your SQL query:
    SELECT FRLOT.LOT_ID, FRLOT.LOT TYPE FROM FRLOT WHERE FRLOT.LOT_ID IN (%{lot})
    Notice that it is a bad practice to pass macros as non parameters, but for now and until more help is coming, I believe that this will work.

    @BalazsBarany might have a better idea on how to solve this, maybe?

    All the best,

    Rod.

Answers

  • Options
    rfuentealbarfuentealba Moderator, RapidMiner Certified Analyst, Member, University Professor Posts: 568 Unicorn
    Hello, @WentingLi13,

    What database are you using? Using simply A or A, B as part of your queries doesn't give anything because most of the time A and without single quotes means you are calling a table (this might vary from database to database).

    To use a macro for IN in your SELECT, you should single quote your values, like this:

    'A', 'B'



    Please, consider the security implications of having free form data in your queries. If this is a production system, you may end up with SQL injections.

    All the best,

    Rod.


  • Options
    WentingLi13WentingLi13 Member Posts: 9 Learner I
    rfuentealba  thanks for your reply.
    I've tried your way, but i still can't get the result, but when i input A or B alone, I can get the result, I don't know where's the problem :(
  • Options
    WentingLi13WentingLi13 Member Posts: 9 Learner I
    edited April 2020
    When i tried like this way, I can't get any result



    When i input a single value, i can get the result

    here is my query sql and set macro setting



  • Options
    WentingLi13WentingLi13 Member Posts: 9 Learner I
    @rfuentealba
    Thank you soooo much, i can get result now, haha
    look forward a better way to solve my problem, thank you all!
  • Options
    rfuentealbarfuentealba Moderator, RapidMiner Certified Analyst, Member, University Professor Posts: 568 Unicorn
    Normally I would encourage users to mark answers as resolved once people get results, but since this is not really a solved answer, please don't do this.
  • Options
    WentingLi13WentingLi13 Member Posts: 9 Learner I
    edited April 2020
    plus, i'm not very clear of "it is a bad practice to pass macros as non parameters", could you explain more for me, thanks!
  • Options
    WentingLi13WentingLi13 Member Posts: 9 Learner I
    BalazsBarany Thanks for your kindly explaination, it's really help!
  • Options
    rfuentealbarfuentealba Moderator, RapidMiner Certified Analyst, Member, University Professor Posts: 568 Unicorn
    edited April 2020
    Hello @WentingLi13,

    Simple explanation:

    If you have this query without a parameter:

    SELECT title, content FROM article WHERE id = %{macro};

    And macro is something like:

    0 OR 1 = 1<br>

    You will make the software fail because the resulting query will be:

    SELECT title, content FROM article WHERE id = 0 or 1 = 1;

    That is called SQL injection, and if you execute that query, you will get all the records from the articles table. Not good.

    With a parameterized query like:

    SELECT title, content FROM article WHERE id = ?

    If you pass the same macro as a parameter, the resulting query will be:

    SELECT title, content FROM article WHERE id = '0 or 1 = 1';

    If you pass a wrong parameter, the worst thing that can happen is that the query won't give you any results. SQL injections are normally directed at giving you access to content from a database that you are not supposed to see. Imagine that instead of articles you want to change passwords... Parameterized queries are the way to prevent those things.

    That's it.

    All the best,

    Rod.
  • Options
    WentingLi13WentingLi13 Member Posts: 9 Learner I
    rfuentealba  Hi, thanks for your explaination about the difference of the parameterized input and non-paramterized, but in my case, parameterized seems not worked, i can't query the data i need, it should have query result, but i got nothing...

    So I finally knew what you meaned yesterday, waiting for a better way to fix my problem, thanks again :) have a nice day





Sign In or Register to comment.