Read Database, query sql in

WentingLi13

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(?)

rfuentealba

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.

BalazsBarany

Hi!

I wouldn't says it's a very bad practice to pass macro values in every case, but it can lead to SQL injection attacks. If you control the inputs of your process, and use a read-only access to you database, not a lot of bad stuff can happen.

If you have a dynamic list of parameters with a specified format, and you'd like to use the prepared query with ? as the parameter placeholder, you can split up these yourself. The functions for this are unfortunately database dependent.

In Postgres you would do it like this:

with inputs as (
	select regexp_split_to_table('1, 2, 3, 4', ', *') as param
)
select param
from inputs

Here you can use WHERE value IN (SELECT param FROM inputs), and you could pass the '1, 2, 3, 4' value as a ? parameter. This setup makes sure that the format and data types are correct, and avoid the danger of SQL injection.

Regards,
Balázs 

rfuentealba

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 B 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.

WentingLi13

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

WentingLi13

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

rfuentealba

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.

WentingLi13

@rfuentealba
Thank you soooo much, i can get result now, haha
look forward a better way to solve my problem, thank you all!

rfuentealba

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.

WentingLi13

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!

BalazsBarany

Hi!

I wouldn't says it's a very bad practice to pass macro values in every case, but it can lead to SQL injection attacks. If you control the inputs of your process, and use a read-only access to you database, not a lot of bad stuff can happen.

If you have a dynamic list of parameters with a specified format, and you'd like to use the prepared query with ? as the parameter placeholder, you can split up these yourself. The functions for this are unfortunately database dependent.

In Postgres you would do it like this:

with inputs as (
	select regexp_split_to_table('1, 2, 3, 4', ', *') as param
)
select param
from inputs

Here you can use WHERE value IN (SELECT param FROM inputs), and you could pass the '1, 2, 3, 4' value as a ? parameter. This setup makes sure that the format and data types are correct, and avoid the danger of SQL injection.

Regards,
Balázs 

WentingLi13

BalazsBarany Thanks for your kindly explaination, it's really help!

rfuentealba

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.

WentingLi13

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