Direct SQL entry and editing in BAQ Designer

(Mike Gross) #1

I think this speaks for itself. Just let us quickly edit/copy into a BAQ the correct SQL. Many of us edit/test SQL in another editor because it is so much easier and then we have to reproduce it graphically.

I’ll even allow the E9 "can’t represent the query graphically’ error for some of the SQL commands…

(Jose C Gomez) #2

I’m gonna negative vote this one… is that an option? NOPE… LoL Don’t want anyone doing direct SQL… ever… nope nope.

(Brandon Anderson) #3

But you have to admit a copy paste in the BAQ editor would be pretty nice sometimes…

(Jose C Gomez) #4

Nope… I do not want any single one of my customers to have this power… EVER… too much at stake. Would I personally like it? sure… but its a terrible idea for 99.99999999% of the customers. IMO

(Brandon Anderson) #5

Why is it any more dangerous than copying it out, and testing it using toad or similar program? Just asking.

(Jose C Gomez) #6

People can write really bad SQL. The BAQ tool mitigates this to some extent, it prevents you from shooting yourself in the foot (as much as it can)
Plus there’s SQL injection and security issues.

(Mike Gross) #7

ok, ok - I definitely see your point… How about we make it another security option. The “Jose-approved” checkbox (for that 0.000000001%) for users to get this function? LOL

(John Mitchell) #8

Remember their SAAS guys all share a single db server too. Even in Azure from my understanding. They would have to move their whole security model to Row Level Security inside of SQL Server instead of just having their own security layer in the app.

I think what you are looking for is some kind of extension or app where you can write SQL and it will turn it into a BAQ file that you could import.

(Mike Gross) #9

Perhaps that is the best solution @John_Mitchell. I’d be more than happy with that.

However, to Jose’s point, would that not have the same effect as pasting SQL into the current editor? This BAQ tool would also have to recognize bad code, injections, and all manner of foul deeds perpetrated by Jose’s user base (couldn’t help it Jose!)

An external program could take advantage of the myriad of SQL ‘check’ code out there…

(John Mitchell) #10

That’s very true. Also, I just looked at a E10 .baq file and it looks like they are compiled now.

(Andris Skulte) #11

(Bart Elia) #12

I’ll also add that BAQ does inject additional stuff into the queries to limit results. Territory, Tenancy, Payroll, etc as appropriate.

(Caleb Grundmeier) #13

Is there a way to bypass this without going to a External BAQ? I know the additional stuff can really degrade the query performance(meaning running the BAQ generated query phrase syntax on the server vs running the BAQ in Epicor is significantly different) and we end up using the External. I’d rather maintain the query in Epicor.

(Bart Elia) #14

No. Breaking security rules in the product probably will not be a feature we add :wink:

(Ed Stang) #15

I think we should all give a measure of thanks that the BAQ Editor gives us a glimpse of the generated SQL at all - whether it is well formed or not. When trying to clean up a mess from a well-intended user’s BAQ or from the “Expert” work delivered by another consultant I have often used that to quickly identify the parts to hack away.

(Bart Elia) #16

Now THAT is an interesting direction. Execution plans, the generated SQL, etc is an interesting conversation to educate people that want to look at what they are doing to the system.

(Brandon Anderson) #17

A reader for the execution plan would be handy so you didn’t have to bring it into another program.

(jayaprakash prabhakaran) #18

perhaps, access to in-line table functions in BAQ designer? External BAQ designer, enables this functionality though.

(James McKinnon) #19

We are on version and off maintenance. We have the Epicor education module and the official Epicor training material that shipped with our version includes exercises where direct connections via visual studio in the form of SQL queries are actively promoted.

Version information below

Revision: March 14, 2013 2:07 a.m.
Total pages: 66

Epicor previously promoted this, is it purely the SAAS implication or some other reason that brought about the change of position by Epicor - we are off maintenance and as we have a strained relationship with Epicor so don’t get invited to anything so most likely missed any communication.

I get the security issues and the sql injection issues, but I think dropping tables, delete data, select * from erp.parttran etc could easily be prevented by having a list of allowable commands within a direct sql editor with a default limit on rows returned and execution time that you have to specifically override.

(Jose C Gomez) #20

:exploding_head::grimacing::scream: really? ugh…