[LP8.6.3] Temporary Tables

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[LP8.6.3] Temporary Tables

Jon Harris
Hi List

I had been struggling to produce some collated data with arrays, pairs and maps, in the end it was easier to write the data into a MySQL table, then use some joins and "order by" to get the data on the page as I need it.

This is now working ok, but the data is only temporary. I was wondering how Lasso could work with temporary (memory tables) in MySQL.  Should I use temporary tables - are they a good idea?

What happens if another user loads that page, with different criteria? Should the temporary table name include a sessionid?

Alternatively, I could use a permanent table and add a session id field to the table and add that to my selection criteria. Anyone have any thoughts on using temporary data?

Any advice appreciated.

Regards
Jon




#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [LP8.6.3] Temporary Tables

stevepiercy
On 6/7/16 at 9:41 AM, [hidden email] (Jon Harris) pronounced:

>This is now working ok, but the data is only temporary. I was
>wondering how Lasso could work with temporary (memory tables)
>in MySQL.  Should I use temporary tables - are they a good idea?

Lasso can issue SQL statements in inlines.

     local(
         'sql' = string,
         'cnxn' = // parameters for connecting to mysql,

         // for SQL select logic
         'select' = string,

         // for other SQL logics
         );

     // logic used to manually build #sql
     #sql -> append("CREATE TEMPORARY TABLE temptable ");

     // SQL select logic
     #sql -> append(#select);

     // other SQL logics
     #sql -> append(... other SQL logics ...);

     inline(#cnxn, -sql=#sql);

Make sure to validate, sanitize, and encode_sql any user
provided input in your logics to prevent SQL injection.  Search
the list archive for why this is a good idea.

The temp table is dropped when the MySQL connection in the Lasso
inline is closed.

>What happens if another user loads that page, with different
>criteria? Should the temporary table name include a sessionid?

Results will be unique for each unique request, and will be
isolated to the MySQL connection session, which is different
from the Lasso session.  "Session" here has two different contexts.

>Alternatively, I could use a permanent table and add a session
>id field to the table and add that to my selection criteria.
>Anyone have any thoughts on using temporary data?

Here's a reasonable discussion from tehGoogle.
http://www.mysqltutorial.org/mysql-temporary-table/

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.stevepiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: [LP8.6.3] Temporary Tables

Jon Harris
Hi Steve

Hi Steve

Thanks for the advice and the link.

We are getting more disciplined with SQL injection prevention - I have had to "retro-fit" a couple of  our sites with encode_sql statements, after running some vulnerability scans. We also wrote a paramfilter tag to look for "<script" "function" etc, to stop XSS issues.

On the temporary table issue - that is good news. I assumed the temporary table would be "seen" by other mysql connection sessions.

- Will give it a go.

Regards
Jon


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Steve Piercy - Website Builder
Sent: 07 June 2016 11:09
To: [hidden email]
Subject: Re: [LP8.6.3] Temporary Tables

On 6/7/16 at 9:41 AM, [hidden email] (Jon Harris) pronounced:

>This is now working ok, but the data is only temporary. I was wondering
>how Lasso could work with temporary (memory tables) in MySQL.  Should I
>use temporary tables - are they a good idea?

Lasso can issue SQL statements in inlines.

     local(
         'sql' = string,
         'cnxn' = // parameters for connecting to mysql,

         // for SQL select logic
         'select' = string,

         // for other SQL logics
         );

     // logic used to manually build #sql
     #sql -> append("CREATE TEMPORARY TABLE temptable ");

     // SQL select logic
     #sql -> append(#select);

     // other SQL logics
     #sql -> append(... other SQL logics ...);

     inline(#cnxn, -sql=#sql);

Make sure to validate, sanitize, and encode_sql any user provided input in your logics to prevent SQL injection.  Search the list archive for why this is a good idea.

The temp table is dropped when the MySQL connection in the Lasso inline is closed.

>What happens if another user loads that page, with different criteria?
>Should the temporary table name include a sessionid?

Results will be unique for each unique request, and will be isolated to the MySQL connection session, which is different from the Lasso session.  "Session" here has two different contexts.

>Alternatively, I could use a permanent table and add a session id field
>to the table and add that to my selection criteria.
>Anyone have any thoughts on using temporary data?

Here's a reasonable discussion from tehGoogle.
http://www.mysqltutorial.org/mysql-temporary-table/

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.stevepiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email] Official list archives available at http://www.lassotalk.com To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [LP8.6.3] Temporary Tables

Bil Corry-3
Just a heads up, XSS is solved with proper context encoding, your black
list won't catch everything, but if it's a second line of defense, then it
can't hurt.


- Bil


On Tue, Jun 7, 2016 at 1:16 PM, Jon Harris <[hidden email]>
wrote:

> Hi Steve
>
> Hi Steve
>
> Thanks for the advice and the link.
>
> We are getting more disciplined with SQL injection prevention - I have had
> to "retro-fit" a couple of  our sites with encode_sql statements, after
> running some vulnerability scans. We also wrote a paramfilter tag to look
> for "<script" "function" etc, to stop XSS issues.
>
> On the temporary table issue - that is good news. I assumed the temporary
> table would be "seen" by other mysql connection sessions.
>
> - Will give it a go.
>
> Regards
> Jon
>
>
> -----Original Message-----
> From: [hidden email] [mailto:
> [hidden email]] On Behalf Of Steve Piercy - Website
> Builder
> Sent: 07 June 2016 11:09
> To: [hidden email]
> Subject: Re: [LP8.6.3] Temporary Tables
>
> On 6/7/16 at 9:41 AM, [hidden email] (Jon Harris) pronounced:
>
> >This is now working ok, but the data is only temporary. I was wondering
> >how Lasso could work with temporary (memory tables) in MySQL.  Should I
> >use temporary tables - are they a good idea?
>
> Lasso can issue SQL statements in inlines.
>
>      local(
>          'sql' = string,
>          'cnxn' = // parameters for connecting to mysql,
>
>          // for SQL select logic
>          'select' = string,
>
>          // for other SQL logics
>          );
>
>      // logic used to manually build #sql
>      #sql -> append("CREATE TEMPORARY TABLE temptable ");
>
>      // SQL select logic
>      #sql -> append(#select);
>
>      // other SQL logics
>      #sql -> append(... other SQL logics ...);
>
>      inline(#cnxn, -sql=#sql);
>
> Make sure to validate, sanitize, and encode_sql any user provided input in
> your logics to prevent SQL injection.  Search the list archive for why this
> is a good idea.
>
> The temp table is dropped when the MySQL connection in the Lasso inline is
> closed.
>
> >What happens if another user loads that page, with different criteria?
> >Should the temporary table name include a sessionid?
>
> Results will be unique for each unique request, and will be isolated to
> the MySQL connection session, which is different from the Lasso session.
> "Session" here has two different contexts.
>
> >Alternatively, I could use a permanent table and add a session id field
> >to the table and add that to my selection criteria.
> >Anyone have any thoughts on using temporary data?
>
> Here's a reasonable discussion from tehGoogle.
> http://www.mysqltutorial.org/mysql-temporary-table/
>
> --steve
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy              Website Builder              Soquel, CA
> <[hidden email]>               <http://www.stevepiercy.com/>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email] Official list archives
> available at http://www.lassotalk.com To unsubscribe, E-mail to: <
> [hidden email]>
> Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>