nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Siemens Community Catalyst Program

The Siemens Community Catalyst program was co-created with our community to acknowledge technology leaders who consistently contribute to the Siemens Community. Nominations are accepted on a rolling basis.

Nominate Now

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

Find more posts tagged with

AI Studio

Databases

SQL

Accepted answers

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.

BalazsBaranyRM

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

All comments

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'

Image: https://us.v-cdn.net/6038102/uploads/editor/hq/3bjqvqnttkwl.png

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

Image: https://us.v-cdn.net/6038102/uploads/editor/1v/fkzryvjna8dm.png

Image: https://us.v-cdn.net/6038102/uploads/editor/ja/6obyqlffoe0l.png

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

Image: https://us.v-cdn.net/6038102/uploads/editor/sb/m84k4ptjbzs5.png

Image: https://us.v-cdn.net/6038102/uploads/editor/02/retgys2s7m1n.png

here is my query sql and set macro setting

Image: https://us.v-cdn.net/6038102/uploads/editor/m1/dq93o4us42us.png

Image: https://us.v-cdn.net/6038102/uploads/editor/81/gycq528q6pyu.png

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})

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!

BalazsBaranyRM

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

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

Image: https://us.v-cdn.net/6038102/uploads/editor/wi/976rsh02oz9m.png

Image: https://us.v-cdn.net/6038102/uploads/editor/ti/69eocyuyjzas.png

Image: https://us.v-cdn.net/6038102/uploads/editor/5r/fha6cz6wvrsy.png

Quick Links

Upcoming Events

Advancing power electronics design with Simcenter PSIM and High-Performance Computing
Join us for a community conversation on how High-Performance Computing (HPC) and Simcenter PSIM work together to meet the increasing demands of modern power electronics simulation. During this one-hour technical session, you'll discover how leveraging HPC can speed up simulations, improve scalability, and empower engineers…
Getting Started with Faster Motor Building in Simcenter Simlab
Join us for a community conversation on accelerating your motor simulation workflow with Simcenter Simlab. In this beginner-friendly session, we'll introduce the platform's automated model-building capabilities and show how easy it is to set up your first electric motor analysis. You'll see the basic steps of geometry…
Practical hybrid solver workflows in Simcenter Feko
Join us for a Community Conversation where we'll discuss practical approaches to analyzing complex electromagnetic models using hybrid solver techniques. This session will provide a general overview of how different computational methods can be combined within a single simulation to address models containing varied…

View all community events