• You are here:  
  • Home
  • Q&A
  • Q&A
  • SnapObjects
  • Best practice to pass a sql select to .net datastore?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

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

  1. How-to
0
Votes
Undo
  1. Simone Olianti
    Simone Olianti
  2. SnapObjects
  3. 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

Editing SampleService.cs for custom scaffolding
Sorting, filtering, and paging - ASP.NET MVC with ...
Accepted Answer
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 1 October 2019 13:13 PM UTC
  2. SnapObjects
  3. # 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
  1. Michael Kramer
  2. 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.
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. 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.
  1. Helpful
Simone Olianti
  1. Simone Olianti
  2. Tuesday, 1 October 2019 14:55 PM UTC
very helpful! thank you Michael!
  1. Helpful
There are no comments made yet.
Responses (4)
francisco Herrera
francisco Herrera Accepted Answer Pending Moderation
  1. Thursday, 21 November 2019 23:26 PM UTC
  2. SnapObjects
  3. # 1

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)

 

 

 

Attachments (1)
pic1.jpg pic1.jpg
Comment
Simone Olianti
  1. Simone Olianti
  2. Friday, 22 November 2019 07:36 AM UTC
thank you Francisco!
  1. Helpful
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 1 October 2019 14:37 PM UTC
  2. SnapObjects
  3. # 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
  1. Simone Olianti
  2. Tuesday, 1 October 2019 14:55 PM UTC
thank you Roland!
  1. Helpful
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 1 October 2019 12:56 PM UTC
  2. SnapObjects
  3. # 3

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
  1. Simone Olianti
  2. 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?

  1. Helpful
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.