Best practice to pass a sql select to .net datastore?

Resolved Best practice to pass a sql select to .net datastore?

How-to

Votes

Undo

Simone Olianti
SnapObjects
Tuesday, 1 October 2019 10:07 AM UTC

hi there! web api newbie here,

i've built a .net datastore model on my web api and created a method to retrieve data and everything working fine.
what i wanted to know now is if there is a way to pass the entire sql syntax of the main select of the ds from the client.

I see there is the useful SetSqlSelect method which could be helpful, and i create my service like this:

        public IDataStore Retrieve(string sqlselect)
        {
            var dss_SearchArt = new DataStore("dws_articolo_l", _dataContext);
            dss_SearchArt.SetSqlSelect(sqlselect, true);
            dss_SearchArt.Retrieve();
            return dss_SearchArt;
        }

everything woking fine if i pass a simple sql syntax, but when i try to pass a more complex and longer syntax i always receive an invalid url error (-2)

//this is working fine
ls_sql = 'select codice, descrizione, id_articolo from articolo' //this is working fine

//this is not working
ls_sql = "SELECT articolo.id_articolo, articolo.id_cat_codifica, articolo.codice, articolo.descrizione, articolo.des_breve, "
ls_sql = ls_sql + " unita_confezione.id_unita_confezione, unita_confezione.codice, unita_confezione.descrizione, "
ls_sql = ls_sql + " sog_commerciale.codice, anagrafica.rag_soc_1, articolo.utilizzabile, articolo.giorni_durata "
ls_sql = ls_sql + " FROM cat_merceologica RIGHT OUTER JOIN articolo "
ls_sql = ls_sql + " ON cat_merceologica.id_cat_merceologica = articolo.id_cat_merceologica, "cat_fiscale RIGHT OUTER JOIN articolo "
ls_sql = ls_sql + " ON cat_fiscale.id_cat_fiscale = articolo.id_cat_fiscale LEFT OUTER JOIN gruppo_articoli "
ls_sql = ls_sql + " ON articolo.id_gruppo_articoli = gruppo_articoli.id_gruppo_articoli LEFT OUTER JOIN gruppo_produzione "
ls_sql = ls_sql + " ON articolo.id_gruppo_produzione = gruppo_produzione.id_gruppo_produzione LEFT OUTER JOIN sog_commerciale "
ls_sql = ls_sql + " ON articolo.id_sog_commerciale = sog_commerciale.id_sog_commerciale LEFT OUTER JOIN anagrafica "
ls_sql = ls_sql + " ON sog_commerciale.id_anagrafica = anagrafica.id_anagrafica, cat_codifica, cat_com_articolo, unita_confezione, par_sistema "
ls_sql = ls_sql + " WHERE (( articolo.codice like '%ART%' ) OR ( articolo.codice is NULL )) AND (( unita_confezione.id_articolo = articolo.id_articolo ) "
ls_sql = ls_sql + " and ( cat_com_articolo.id_cat_com_articolo = articolo.id_cat_com_articolo ) and ( cat_codifica.id_cat_codifica = articolo.id_cat_codifica ) "
ls_sql = ls_sql + " and (( unita_confezione.unita_base = 'S' ))) "

ls_Request_Url = "https://localhost:5001/api/SearchArt/retrieve?sqlselect='" + ls_sql + "'"
inv_RestClient.sendgetrequest(ls_Request_Url, ls_response)
importjson(ls_response)

what is the best practice to build the url in this case? i've also tried to eventually use escape characters to build the url but without success. What am i missing?
not sure if i am approaching this in the right way.

tia, simone

Accepted Answer

Michael Kramer Accepted Answer Pending Moderation

Tuesday, 1 October 2019 13:13 PM UTC
SnapObjects
# Permalink

Hi Simone,

You seem to have a potentially too long query string. Web standard components (like browsers and servers) have restriction on URL length though. You should consider using POST instead of GET.

Common recommendation is keep URL (incl. query string) below 2000 bytes. Preferably way below.

In any case: To reduce attack surface, avoid sending tech. details like query string fragments. Instead send data that server code can use to rewrite actual SQL. Risk: Man-in-the-middle attacker can read your internal database structure from reading your URLs.

We need to rethink how we expose system internals when part of network and servers is off premises instead of secure LAN.

HTH /Michael

Comment

Load more comments

Michael Kramer
Tuesday, 1 October 2019 14:33 PM UTC

Simone, I would NEVER send object names, nor column names. They expose internal details on structure.

I would send NVO/struct which "on the wire" becomes JSON. So something like {"custname_filter": "charl", ...}.

That info can be translated by server code into "WHERE …" + " AND (UPPER(customer.FullName)" LIKE '%CHARL%')"

I'm presenting on how to build secure apps at ELEVATE 2019. There I will have code examples.

Helpful 0

Michael Kramer
Tuesday, 1 October 2019 14:36 PM UTC

Morale of the story: Expect anything your app sends to a web-service will be published sometime in the future for all web to have a look. SQL injection is a nightmare that CAN be avoided.

Helpful 0

Simone Olianti
Tuesday, 1 October 2019 14:55 PM UTC

very helpful! thank you Michael!

Helpful 0

There are no comments made yet.

Responses (4)

Roland Smith Accepted Answer Pending Moderation

Tuesday, 1 October 2019 12:56 PM UTC
SnapObjects
# 1

The best practice would be to not pass entire SQL statements. This would open up the possibility of a 'SQL Injection' attack. Some hacker could send a SQL statement like DELETE * FROM CUSTOMER and if your database had a table with that name, the statement would be executed.

To get it to work, you need to encode the SQL in a way that is valid for a URL.

Comment

Simone Olianti
Tuesday, 1 October 2019 14:14 PM UTC

Thank you roland, precious advices as always.

better if i send the filter fragments of a select instead all of it. Do you think would be better if i crypt the part of the select before passing it and then decryt server side before using it?

Helpful 0

There are no comments made yet.

Roland Smith Accepted Answer Pending Moderation

Tuesday, 1 October 2019 14:37 PM UTC
SnapObjects
# 2

PowerBuilder 2017 and higher have the CoderObject object which has UrlEncode and UrlDecode methods.

The .Net side should be able to do an UrlDecode.

Comment

Simone Olianti
Tuesday, 1 October 2019 14:55 PM UTC

thank you Roland!

Helpful 0

There are no comments made yet.

francisco Herrera Accepted Answer Pending Moderation

Thursday, 21 November 2019 23:26 PM UTC
SnapObjects
# 3

Dear Simone

As mentioned before sending such a long URL is not a convenient way to proceed. Plus, sending Raw SQL statements can be dangerous. However, if you still want to proceed like so, then we consider these recommendations may help you with sending a large string parameter:

CONTROLLER

The use of [FromBody] which is a Web Api Bind Parameter so you can send any parameter or variable, to the web api. It will be sent on the body of the request leaving the URL untouched.

 // POST api/customers

        [HttpPost]

        [ProducesResponseType(StatusCodes.Status200OK)]

        [ProducesResponseType(StatusCodes.Status404NotFound)]

        [ProducesResponseType(StatusCodes.Status500InternalServerError)]

        public ActionResult<int> Post([FromBody] string rawSQL)

try

                    var response = _service.Add(rawSQL);

                   if (response <=0)
                      { 
                          return StatusCode(404);
                      }
                   else
                     {

                       return StatusCode(200, response);

            catch (Exception ex)

                return StatusCode(500, ex.Message);

More info on [FromBody]: https://docs.microsoft.com/en-us/aspnet/web-api/overview/formats-and-model-binding/parameter-binding-in-aspnet-web-api

Sending the request using Postman

(Check the attached image)

Comment

Simone Olianti
Friday, 22 November 2019 07:36 AM UTC

thank you Francisco!

Helpful 0

There are no comments made yet.

Page :
1

There are no replies made for this question yet.
However, you are not allowed to reply to this question.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved Best practice to pass a sql select to .net datastore?

Find Questions by Tag