Executing a query given from front-end.

(0) Share

Report

Posted on by Community Member

Hi I have a problem in which I have a string which is my query.

like "Select * from CustTable where CustTable .AccountNum=="Acc-001" " (hope this is clear that i am receiving my query in a string).

Now is there any way that I can execute this query in X++. Where i can just pass this string that is actually my query to some object/variable/function which executes it and returns me a buffer or a query object which I can iterate and get all the records returned as result.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Martin Dráb 237,912 Most Valuable Professional on at

Like (0)

Report

We're already discussing it in your other thread, I want to create a dynamic query. Can I use the sysQueryForm?. Why did you create this one?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

This is specifically about the string requirement so I created this.

I thought I was asking a wrong question.

Was this reply helpful? Yes No
Martin Dráb 237,912 Most Valuable Professional on at

Like (0)

Report

We could split the original thread if needed, but discussing the same thing at two places makes things more complicated, not easier. I'm going to continue the discussion where we started it; I won't copy my replies here.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

void Sample_1(void)

{

Connection Con = new Connection();

Statement Stmt = Con.createStatement();

ResultSet R =Stmt.executeQuery(‘SELECT VALUE FROM SQLSYSTEMVARIABLES’);

while ( R.next() )

{

print R.getString(1);

}

}

Was this reply helpful? Yes No
Martin Dráb 237,912 Most Valuable Professional on at

Like (0)

Report

I'll copy my answer here is well, in case that somebody found this thread and got an impression that this is a reasonable approach. (Note that the other thread contains my suggestion how to achieve the goal without giving up control over the database.)

No, it doesn't. It creates a huge amount of problems. There are very good reasons why developers shouldn't access database directly and you even want to give it to all users! I'm sorry for the client, because they're heading to a disaster...

Have you configured security roles in your system? Now you can forgot them, anybody can do anything. Even things that AOS wouldn't ever allow, such as completely destroying integrity of the database.

There are many other problems, such as that users would have to understand extra details of the model (partitions, data area ID, relations, implantation of table inheritance), it bypasses caching and so on, but it's all trivial on comparison with that the fact that anybody will be able to do anything, regardless of all business logic and security setup.

Good luck - you'll need it.

Please come back if you realize that you need a smarter and safer solution (but don't ask me to repair the database).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Martin...I understand all the things you said and yes you are completely right this is not the solution for the masses.

I said that there is no security breach because in my case there wasn't.

This solution which I'm creating is particularly only for my use....NO one is given access from the front end to use this...

It's only for internal use.

I just wanted a solution for that.

And the executeQuery solved my problem.

Before this I had no idea that this way existed or is even possible ... many reporting problems can be solved using this as well...

So yes it's a good thing that you are creating awareness but I assure you I'm not making this for the people it's just for internal use. Not going to be deployed over to any other instance...

Happy? :)

Was this reply helpful? Yes No
Martin Dráb 237,912 Most Valuable Professional on at

Like (0)

Report

I never said that there was a security breach, I'm saying that your new "solution" can be used to bypass all security and business logic.

You now claim that "NO one is given access from the front end", but before you said the exact opposite: "the user can construct his own SQL query from the front end". And even if it was used only by experienced developers, bypassing security, all business logic, compile-time control and so on is still a horrible idea.

When you say "many reporting problems can be solved using this", I read "we are going to abuse this on regularly basis", so I've clearly failed in explaining why it's such a bad thing... :-(

Was this reply helpful? Yes No

Community site session details

Executing a query given from front-end.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Executing a query given from front-end.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates