search

denisenkom / go-mssqldb

Microsoft SQL server driver written in go language
BSD 3-Clause "New" or "Revised" License
1.82k stars 495 forks source link

Is it possible to distinguish between stored procedure calls, prepared SQL calls, and ad-hoc SQL calls? #683

Open sql-sith opened 3 years ago

sql-sith commented 3 years ago

I think the topic/title sums up my question. We are building a service that acts as an API gateway to SQL Server. We want it to allow stored procedure calls but not allow pure ad-hoc SQL calls. Bonus points if we can also identify prepared/properly parameterized SQL calls.

Is this possible?

kardianos commented 3 years ago

Yeah. Extract from the code the part that looks to see if the SQL text is a stored parameter or not.

AS for the other one, no, not really.

You are probably aware, but you can pass in a proc that says "execute this parameter as SQL"... So there's that.

sql-sith commented 3 years ago

Thanks, @kardianos . Do you mean to extract the code for the method isProc()? Parsing this out like that is difficult, and as I'm looking at that code, I'm not sure it would catch every case of a stored procedure. I have a list of forms (not exhaustive) that we need to look for if we are parsing.

Some providers/frameworks have different methods for calling stored procedures, prepared statements, and ad-hoc SQL (aka "strings"). For example, .NET has this enum where you can declare which batch type you are calling. If we had that, we could allow only CommandType = 4 and we'd be done, because it would force apps to specifically use stored procedures. That is what I was hoping to find in golang. Is there anything like this? Or is our best case just to call isProc ourselves and hope for the best/tweak as needed?