4

Is it possible to call a stored procedure within javascript on the client side?

I know how to do on the server side, but I am interesting in doing on the client side.

Basically it boils down to directly contacting a SQL server from within the client. Is that possible?

askolotl
  • 964
  • 1
  • 13
  • 27
  • It depends. Do you WANT hackers to pwn your database? Never put SQL in the client. – 4castle Apr 02 '16 at 21:04
  • @4castle Now what does that mean *"it depends"*? Is it technically possible or not? I want to contact a SQL Server, directly from the client, call a stored procedure and read the results. Now is that basically possible or not? – askolotl Apr 02 '16 at 21:12
  • @4castle I know about security holes. Let that be my problem. that will be used in an intranet only anyway. I just want to know if you can connect to a sql server directly from within the client or not. If you know the answer for sure, please post it as an answer, I will happily mark it as correct answer. – askolotl Apr 02 '16 at 21:15
  • I'm not sure what you mean by "directly." Is the database stored on the client? Otherwise, no, there's no way to "directly" access a database over HTTP without some kind of middle-ware on the server. – 4castle Apr 02 '16 at 21:18
  • @4castle Yes, I mean to directly connect the client o the SQL server, without a middleware. Technically speaking, the client should open a connection to the sql server. Is this basically possible, or is javascript sitting in a sandbox which allows no outbreak? – askolotl Apr 02 '16 at 21:21
  • It is not possible. The devices are separated by HTTP, and SQL Server doesn't speak HTTP. – 4castle Apr 02 '16 at 21:34

4 Answers4

4

tldr; No, it is not possible to connect to SQL Server 'directly' from browser-JavaScript1.

JavaScript can "speak" HTTP and WebSockets, but SQL Server "speaks" TDS. To communicate there needs to be a common medium/protocol that both the client and server use.

  • While there are WebSocket proxies that technically make this possible it still requires a separate proxy service (and you'd still have to write/find a JavaScript TDS driver). I don't recommend eliminating the controlled access layer.

  • Likewise, an HTTP proxy where raw SQL commands are sent to/from the client could be used. I wouldn't advise this either, but some do exist.

  • External code/libraries (eg. ActiveX, Java) could establish the SQL connection and proxy through to the JavaScript client.

In all of these cases there is an intermediate helper and browser-JavaScript never connects 'directly'.

1 JavaScript is a language and this answer focuses on a browser implementation with browser-supported libraries/functions. One could argue that using node modules would still 'be JavaScript', and they would be correct .. in a different environment.

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
  • OK, but Websocket is based on TCP. Is it possible to reach through down to TCP ? – askolotl Apr 02 '16 at 21:24
  • BTW, sorry cannot upvote, because I need 15 reps. Will do later when I have the points. – askolotl Apr 02 '16 at 21:25
  • @askolotl No, as the linked question explains :} – user2864740 Apr 02 '16 at 21:25
  • Thanks a lot for the detailed answer. But still, concerning that **"websocket proxy"**: Would that be running on the client side? Does it need any special installation on the client side, or is a standard web browser sufficient? Let's assume I do all the protocol coding myself - is it technically possible? And please, can you maybe look at this project: Is that running on the client side only? https://github.com/kanaka/websockify – askolotl Apr 02 '16 at 21:38
  • @askolotl Such a proxy could run on the client .. probably as a third-party (native) executable, running outside the browser. Or on a remote server. In that project it is the websockify *executable* (Python, with C extensions) that is the proxy which must be running. – user2864740 Apr 02 '16 at 22:05
  • Thanks a lot. I understand the underlying architecture much better now. So basically, without an external proxy of any kind, a direct connection is not possibly. Have also read some articles now, there is the "same origin" principle which also would prohibit a direct connection. – askolotl Apr 02 '16 at 22:12
1

You cannot establish a direct connection to a database from a client's web browser. What you will need to do is create a server side application to expose an API for getting the data over HTTP.

Take a look at Microsoft's ASP.NET Web API

Jesse Lee
  • 1,291
  • 1
  • 8
  • 20
  • Is that restriction documented somewhere? – askolotl Apr 02 '16 at 21:09
  • I don't know that 'restriction' is the correct word for this, it's more of a technical limitation. Client-side JavaScript code is downloaded and executes on the end user's machine. How would that machine connect to a database hosted on your server? – Jesse Lee Apr 02 '16 at 21:12
  • Exactly, it boils down to the question: *Is is possible to connect to another machine over the network within javascript?* Let's put the protocol aside. Let's assume I code that all myself. But is it basically possible to connect and read/write over the network at all? – askolotl Apr 02 '16 at 21:17
  • BTW, sorry cannot upvote, because I need 15 reps. Will do later when I have the points. – askolotl Apr 02 '16 at 21:25
1

Sort of

You could create an endpoint that is a wrapper for stored procedure(s) that takes the procedure name as a parameter, as well as the parameters for the procedure.

Once you have such a mechanism in place, you can create endpoints that expose procedures automagically.

http://yourserver/services/yourprocname?prm1=val,prm2=val,etc

If you feel really ambitious you can try out SQL 2016 and return JSON directly from those procedures. Then you can nest data using subqueries and return the JSON in a single payload. No serialization, no objects, just read the data and return it.

< 2016 you could put the results into a Dictionary and use NewtonSoft to serialize it. Assuming you are returning flat data you'd be good to go. Just use a reader and get the meta data from the column names for the key, and the value as object. NewtonSoft will convert that into JSON for you.

If you are returning hierarchical you could (by convention) create a series of runners that take the reader, and pump it into a Dictionary where object is another Dictionary Again the Newtonsoft stuff will help you out with the serialization.

Hope this helps, we are using this approach with 2016 and it is very nice to be able to create a stored procedure and call it without any middle tier code, deployment, etc. It just works.

Hope this helps.

Vincent
  • 842
  • 7
  • 13
  • 1
    Thank you for the explanation, and for pointing out the JSON capability of SQL 2016! I will upvote as soon as I have 15 reps (needed for upvoting). Well OK, basically it still means that you have to create a proxy (endpoint) which is on the server. You cannot go stand-alone (= from the client alone). I want to create a flexible client solution for intranets. But after all, I see through it now, and a proxy-based solution should be possible as well. – askolotl Apr 03 '16 at 07:12
1

Yes, you can connect to SQL Server from Client side directly by using the WebAssembly. You can write your function that calls the SQL Server in C or C++ first. Compile it to .Wasm by Emscripten compiler. Then you can call the C or C++ code by using JavaScript. In future, you should be able to do that with C# but Microsoft just started work on that. I am writing a post about it, and I will share it when it's ready. Now just because you can do it, doesn't mean you should because of security issues. But I am not here to give a lecture about what you should or should not do.

Hasan Savran
  • 367
  • 2
  • 5
  • Here is the link to my blog about how to call SQL Server from Front-End without calling any server side code. http://h-savran.blogspot.com/2018/02/stey-by-step-running-c-in-your-web-page.html#!/2018/02/stey-by-step-running-c-in-your-web-page.html – Hasan Savran Feb 28 '18 at 12:52