ds -> join example

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

ds -> join example

stevepiercy
I looked for, but did not find, an example for ds -> join.
https://github.com/zeroloop/ds/wiki/Active-Statements#--join

I assume that it should perform a simple left join.  I read the
source of statement.lasso, tried a few guesses, nothing worked.

I resorted to ds -> sql, which worked, but wondered if I missed something.

--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: ds -> join example

Justin Dennis-3
I am interested in this too. The few times I've wanted to leverage ds for
something a bit complicated, I've resorted to ->sql(). I feel like a better
understanding of this would allow much more use of ds. -Justin

On Fri, Jul 10, 2015 at 7:05 AM, Steve Piercy - Website Builder <
[hidden email]> wrote:

> I looked for, but did not find, an example for ds -> join.
> https://github.com/zeroloop/ds/wiki/Active-Statements#--join
>
> I assume that it should perform a simple left join.  I read the source of
> statement.lasso, tried a few guesses, nothing worked.
>
> I resorted to ds -> sql, which worked, but wondered if I missed something.
>
> --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: ds -> join example

Jolle Carlestam-2
In reply to this post by stevepiercy
10 jul 2015 kl. 13:05 skrev Steve Piercy - Website Builder <[hidden email]>:
>
> I looked for, but did not find, an example for ds -> join.
> https://github.com/zeroloop/ds/wiki/Active-Statements#--join
>
> I assume that it should perform a simple left join.  I read the
> source of statement.lasso, tried a few guesses, nothing worked.

Here’s a working example:

define help_category => ds(::mysql.help_category)

(with row in help_category -> select(::help_topic.name, ::help_topic.example) -> join('mysql.help_topic') -> where('help_topic.help_category_id = help_category.help_category_id', ::help_category.name = 'Logical operators') -> rows select '<strong>' + #row(::name) + ':</strong><br /><pre>' + #row(::example) + '</pre>') -> join('<hr />’)

Here’s how to check the actual sql statement being produced:

help_category -> select(::help_topic.name, ::help_topic.example) -> join('mysql.help_topic') -> where('help_topic.help_category_id = help_category.help_category_id', ::help_category.name = 'Logical operators') -> statement

->
SELECT help_topic.name,help_topic.example FROM help_category JOIN mysql.help_topic WHERE help_topic.help_category_id = help_category.help_category_id AND help_category.name = 'Logical operators’

Some reflections:
As you can see it is not a LEFT JOIN but a JOIN.
I find the construction at least as complicated as writing the sql statement myself. Both Steve and Justin writes that they ”resort” to write sql. For me it’s not a resort. I tend to do sql as soon as the request is anything but "get me the record". Like the feeling of control it provides. And I fool myself into thinking that it also provides some speed benefits. After all, all you’re doing is asking DS to create an sql statement for you. Why the detour? When the result is not what you’re expecting you’ll need to do the -> statement anyway and examine the sql to see what went wrong.

Whenever I need to do a query that is complicated I write it in Navicat first. When the result there is what I need I copy the query into my Lasso file and replace whatever dynamic parts it contains with properly sql encoded params.

HDB
Jolle

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

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: ds -> join example

stevepiercy
On 7/11/15 at 8:00 AM, [hidden email] (Jolle Carlestam) pronounced:

>Whenever I need to do a query that is complicated I write it in
>Navicat first. When the result there is what I need I copy the
>query into my Lasso file and replace whatever dynamic parts it
>contains with properly sql encoded params.

This is what I do, too.  It's the encode_sql stuff that I find
tedious.  I hoped that ds would give that to me for complex
JOINs with relative ease.  And a pony.

--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: ds -> join example

Ke Carlton-3
In reply to this post by Justin Dennis-3
I do so like this:

with row in my_ds->select(
::t1.id
::t2.somecolumn
::t2.anothercol
 )->from(`
table1 as t1
INNER JOIN table2 t1 ON t2.parent_id = t1.id
`)->where(
::t1.id = array(1,2,3),
::t2.status = 1
 )->rows do { }

This is also valid:

with row in my_ds->select(
::t1.id
::t2.somecolumn
::t2.anothercol
 )->from(`
table1 as t1
`)->join(`
table2 t1 ON t2.parent_id = t1.id
`)->where(
::t1.id = array(1,2,3),
::t2.status = 1
 )->rows do { }

The first example allows for LEFT joins.

In pretty much 100% of cases now I use active statements over straight SQL—
more often than not I end up converting them over once I need dynamic
values or similar, so I just do so immediately now.

The encoding is handled automatically and it's fairly smart. For example ::
t1.id = array(1,2,3) becomes t1.id IN(1,2,3) and each value will be
appropriately encoded.

Converting a SQL statement into this structure is also very quick, for
example the columns can also be specified like so:

with row in my_ds->select(`
t1.id,
t2.somecolumn,
t2.anothercol
`)->from(`
table1 as t1
INNER JOIN table2 t1 ON t2.parent_id = t1.id
`)->where(
::t1.id = array(1,2,3),
::t2.status = 1
 )->rows do { }

You can provide the where method with an array of key pairs (col = value)
and it will encode accordingly. You can also call ->where multiple times to
expand on the where clause.

Another thing worth pointing out is that they don't need to be invoked
immediately:

local(sql) = my_ds->select(
::t1.id
::t2.somecolumn
::t2.anothercol
 )->from(`
table1 as t1
INNER JOIN table2 t1 ON t2.parent_id = t1.id
`)

#something
? #sql->where(::t2 = 'Yes!')
| #sql->where(::t2 = 'No.')

#anotherthing
? #sql->orderby('this ASC')
| #sql->orderby('that DESC')

#max
? #sql->limit(#skip,#max)

debug('Statement' = #sql->statement)
debug('Found' = #sql->count)

return #sql->rows


Once logic similar to the above comes into play, that's when active
statements start earning their living...

Ke

On Sat, Jul 11, 2015 at 1:46 PM Justin Dennis <[hidden email]> wrote:

> I am interested in this too. The few times I've wanted to leverage ds for
> something a bit complicated, I've resorted to ->sql(). I feel like a better
> understanding of this would allow much more use of ds. -Justin
>
>

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

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: ds -> join example

stevepiercy
Thank you for these examples.  Active statements are shweet.

Expanding on the discussion, regarding encoding, I've been using
->encodesql and ->knop_encodesql_full before passing values into
manually constructed SQL statements.

     "moosehair's underscore _ percent %"->encodesql
     '\n'
     "moosehair's underscore _ percent %"->knop_encodesql_full
     =>
     moosehair\'s underscore _ percent %
     moosehair\'s underscore \_ percent \%

Does ds handle that kind of encoding?  I didn't find it in the
docs, although I found something in the source I don't fully comprehend.
https://github.com/zeroloop/ds/blob/master/statement.lasso#L101-L120

One more question, does ds have something that can encode to !=
or <> operators?  e.g.,

     -> where(::moose .ne. 'hair')

=>
     WHERE `moose` <> 'hair'

--steve


On 7/13/15 at 3:55 AM, [hidden email] (Ke Carlton) pronounced:

>I do so like this:
>
>with row in my_ds->select(
>::t1.id
>::t2.somecolumn
>::t2.anothercol
>)->from(`
>table1 as t1
>INNER JOIN table2 t1 ON t2.parent_id = t1.id
>`)->where(
>::t1.id = array(1,2,3),
>::t2.status = 1
>)->rows do { }
>
>This is also valid:
>
>with row in my_ds->select(
>::t1.id
>::t2.somecolumn
>::t2.anothercol
>)->from(`
>table1 as t1
>`)->join(`
>table2 t1 ON t2.parent_id = t1.id
>`)->where(
>::t1.id = array(1,2,3),
>::t2.status = 1
>)->rows do { }
>
>The first example allows for LEFT joins.
>
>In pretty much 100% of cases now I use active statements over straight SQL—
>more often than not I end up converting them over once I need dynamic
>values or similar, so I just do so immediately now.
>
>The encoding is handled automatically and it's fairly smart. For example ::
>t1.id = array(1,2,3) becomes t1.id IN(1,2,3) and each value will be
>appropriately encoded.
>
>Converting a SQL statement into this structure is also very quick, for
>example the columns can also be specified like so:
>
>with row in my_ds->select(`
>t1.id,
>t2.somecolumn,
>t2.anothercol
>`)->from(`
>table1 as t1
>INNER JOIN table2 t1 ON t2.parent_id = t1.id
>`)->where(
>::t1.id = array(1,2,3),
>::t2.status = 1
>)->rows do { }
>
>You can provide the where method with an array of key pairs (col = value)
>and it will encode accordingly. You can also call ->where multiple times to
>expand on the where clause.
>
>Another thing worth pointing out is that they don't need to be invoked
>immediately:
>
>local(sql) = my_ds->select(
>::t1.id
>::t2.somecolumn
>::t2.anothercol
>)->from(`
>table1 as t1
>INNER JOIN table2 t1 ON t2.parent_id = t1.id
>`)
>
>#something
>? #sql->where(::t2 = 'Yes!')
>| #sql->where(::t2 = 'No.')
>
>#anotherthing
>? #sql->orderby('this ASC')
>| #sql->orderby('that DESC')
>
>#max
>? #sql->limit(#skip,#max)
>
>debug('Statement' = #sql->statement)
>debug('Found' = #sql->count)
>
>return #sql->rows
>
>
>Once logic similar to the above comes into play, that's when active
>statements start earning their living...
>
>Ke
>
>On Sat, Jul 11, 2015 at 1:46 PM Justin Dennis <[hidden email]> wrote:
>
>>I am interested in this too. The few times I've wanted to leverage ds for
>>something a bit complicated, I've resorted to ->sql(). I feel like a better
>>understanding of this would allow much more use of ds. -Justin
>>
>>
>
>#############################################################
>
>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]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
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: ds -> join example

Jolle Carlestam-2
13 jul 2015 kl. 06:44 skrev Steve Piercy - Website Builder <[hidden email]>:

>
> Expanding on the discussion, regarding encoding, I've been using
> ->encodesql and ->knop_encodesql_full before passing values into
> manually constructed SQL statements.
>
>     "moosehair's underscore _ percent %"->encodesql
>     '\n'
>     "moosehair's underscore _ percent %"->knop_encodesql_full
>     =>
>     moosehair\'s underscore _ percent %
>     moosehair\'s underscore \_ percent \%
>
> Does ds handle that kind of encoding?  I didn't find it in the
> docs, although I found something in the source I don't fully comprehend.
> https://github.com/zeroloop/ds/blob/master/statement.lasso#L101-L120
>
> One more question, does ds have something that can encode to !=
> or <> operators?  e.g.,
>
>     -> where(::moose .ne. 'hair')
>
> =>
>     WHERE `moose` <> 'hair'

Knop_encodesql_full is only relevant when using LIKE queries or similar constructs.
DS -> where does not have any ready made support for LIKE or anything but straight column = value constructs. If you want that you will have to supply the sql snippet yourself, and thus also take responsibility for doing proper sql encoding on it.

define my_ds => ds(::mysql.help_topic)

my_ds->select(::name, ::example)->where(
`name LIKE 'W%'`
)-> statement
-> SELECT name,example FROM help_topic WHERE name LIKE 'W%’

Or, with a dynamic input and proper encoding applied:

local(input = "W%'; DROP TABLE mysql.user;-- ") // aha, Someone's trying to sql inject

my_ds->select(::name, ::example)->where(
`name LIKE '` + (#input -> knop_encodesql_full) + `%'`
)-> statement
-> SELECT name,example FROM help_topic WHERE name LIKE 'W\%\'; DROP TABLE mysql.user;-- %’

Note, if you DON’T do the sql encoding above you’re leaving the gate wide open for sql injection attacks. The way DS builds statements forces it to not apply sql encoding on all inputs. Only inputs deemed to be values are encoded. This is something the developer need to keep in mind when supplying dynamic inputs in sql snippets, database or table names, from or join snippets and possibly others.

With a variation of the above without sql encoding:
my_ds->select(::name, ::example)->where(
`name LIKE '` + #input + `%'`
)-> statement
-> SELECT name,example FROM help_topic WHERE name LIKE 'W%'; DROP TABLE mysql.user;-- %’

Look, Ma, I completely eradicated the user table from the school/bank/government database!

HDB
Jolle

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

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: ds -> join example

Ke Carlton-3
In reply to this post by stevepiercy
At the moment the encoding does not account for LIKE at all. Everything is
either:  =, IS or IN — so escaping underscores etc is a non-issue.

When you need a LIKE expression or something custom you can do so like this:

->where(
` t1.name LIKE "` + #somevalue->knop_encodesql_full + `%" `
::t2.status = 1
)

Another example would be a MATCH clause:

->where(
` MATCH (name,keywords) AGAINST ("+sanitised +input" IN BOOLEAN MODE) `
::t2.status = 1
)

This is one of the ways that active statements are able to support any SQL
datasource, they try to avoid any SQL specific to a certain datasource —
but can handle it via accepting raw string components.

I like the idea of automatic handling of the different types of
expressions, although time would need to be taken in terms of the syntax
and ensuring cross compatibility.

Ke

On Mon, Jul 13, 2015 at 4:46 PM Steve Piercy - Website Builder <
[hidden email]> wrote:

> Thank you for these examples.  Active statements are shweet.
>
> Expanding on the discussion, regarding encoding, I've been using
> ->encodesql and ->knop_encodesql_full before passing values into
> manually constructed SQL statements.
>
> Does ds handle that kind of encoding?  I didn't find it in the
> docs, although I found something in the source I don't fully comprehend.
> https://github.com/zeroloop/ds/blob/master/statement.lasso#L101-L120
>
> One more question, does ds have something that can encode to !=
> or <> operators?  e.g.,
>
>      -> where(::moose .ne. 'hair')
>
> =>
>      WHERE `moose` <> 'hair'
>
> --steve
>

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

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: ds -> join example

Jolle Carlestam-2
In reply to this post by Jolle Carlestam-2
13 jul 2015 kl. 07:49 skrev Jolle Carlestam <[hidden email]>:
>
>> One more question, does ds have something that can encode to !=
>> or <> operators?  e.g.,
>>
>>    -> where(::moose .ne. 'hair')
>>
>> =>
>>    WHERE `moose` <> 'hair'
>
I indirectly answered that question too.

> DS -> where does not have any ready made support for anything but straight column = value constructs. If you want that you will have to supply the sql snippet yourself

Instead of:
 -> where(::moose .ne. 'hair’)
you do
 -> where("`moose` <> ’hair’")

HDB
Jolle

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

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: ds -> join example

Ke Carlton-3
In reply to this post by Jolle Carlestam-2
Yes — what Jolle says, it's important to note that any time you supply raw
SQL (strings vs pairs) you're responsible for the encoding.

So this is always safe:

->where(
::somecolumn = #anyvalue
)

But this is up to you to encode / make safe just like with SQL:

->where(
` myfunkystuff LIKE ` + #saywhat
)

Flexibility does come with a price associated with it.

Ke

On Mon, Jul 13, 2015 at 5:49 PM Jolle Carlestam <[hidden email]> wrote:

> Knop_encodesql_full is only relevant when using LIKE queries or similar
> constructs.
> DS -> where does not have any ready made support for LIKE or anything but
> straight column = value constructs. If you want that you will have to
> supply the sql snippet yourself, and thus also take responsibility for
> doing proper sql encoding on it.
>
> Look, Ma, I completely eradicated the user table from the
> school/bank/government database!
>
> HDB
> Jolle
>
>

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

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: ds -> join example

stevepiercy
In reply to this post by Jolle Carlestam-2
On 7/13/15 at 7:49 AM, [hidden email] (Jolle Carlestam) pronounced:

>With a variation of the above without sql encoding:
>my_ds->select(::name, ::example)->where(
>`name LIKE '` + #input + `%'`
>)-> statement
>-> SELECT name,example FROM help_topic WHERE name LIKE 'W%'; DROP TABLE mysql.user;-- %’

Validating and sanitizing all user input is always a good idea.  
Don't trust users to do the right thing.

Another way to prevent these attacks is not to grant Lasso's
mysql user privileges greater than what they need.  IOW, this is
really a bad idea when creating your mysql users:

mysql> grant all privileges on *.* to 'lassouser'@'%' identified
by 'lassopass';

This is much better:

mysql> grant SELECT, INSERT, UPDATE, DELETE on
one_specific_database.* to 'lassouser'@'%' identified by 'lassopass';

Thus an attacker could not DROP or ALTER objects through this
vector, nor create or add privileges via the GRANT option.

-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: ds -> join example

Jolle Carlestam-2
13 jul 2015 kl. 08:26 skrev Steve Piercy - Website Builder <[hidden email]>:

>
> On 7/13/15 at 7:49 AM, [hidden email] (Jolle Carlestam) pronounced:
>
>> With a variation of the above without sql encoding:
>> my_ds->select(::name, ::example)->where(
>> `name LIKE '` + #input + `%'`
>> )-> statement
>> -> SELECT name,example FROM help_topic WHERE name LIKE 'W%'; DROP TABLE mysql.user;-- %’
>
> Validating and sanitizing all user input is always a good idea.  
> Don't trust users to do the right thing.
>
> Another way to prevent these attacks is not to grant Lasso's
> mysql user privileges greater than what they need.  IOW, this is
> really a bad idea when creating your mysql users:
>
> mysql> grant all privileges on *.* to 'lassouser'@'%' identified
> by 'lassopass';
>
> This is much better:
>
> mysql> grant SELECT, INSERT, UPDATE, DELETE on
> one_specific_database.* to 'lassouser'@'%' identified by 'lassopass';
>
> Thus an attacker could not DROP or ALTER objects through this
> vector, nor create or add privileges via the GRANT option.
>
> -steve

I do not argue any of Steves points here. In fact this is how my own setups are done. Merely underlining that without proper sql injection protection it is still possible for the evildoer to do evil. Here’s another attack example, almost as severe and not protected by the restricted privileges Steve proposes:

local(input = "W%'; DELETE FROM one_specific_database.a_table; DELETE FROM one_specific_database.another_table;-- ”)

Of course, the evildoer would need to know the names of the tables to delete all records from. But hands on heart, how many of you does not have a table called users, or items/products/customers etc. The evildoer could supply a long string of possible table names and be happy to hit with some of them.

HDB
Jolle

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

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: ds -> join example

stevepiercy
In reply to this post by Ke Carlton-3
OK, thanks for confirming the lack of encoding for LIKE.  So the
other two scenarios:

* I assume that ->sql("mystring of SQL") is passed through raw
and not encoded, correct?  I've been using ->encodesql and
->knop_encodesql_full in ds for these cases.

* Is there any encoding of values in ds whatsoever?  It looks
like ds encodes single quotes.

user_ds -> where(::username = "bullwinkle'; DROP TABLE
mysql.user_gone; -- everything after the double-hyphen is
commented out") -> statement

=>
SELECT * FROM users WHERE username = 'bullwinkle\'; DROP TABLE
mysql.user_gone; -- everything after the double-hyphen is
commented out'

--steve


On 7/13/15 at 5:51 AM, [hidden email] (Ke Carlton) pronounced:

>At the moment the encoding does not account for LIKE at all. Everything is
>either:  =, IS or IN — so escaping underscores etc is a non-issue.
>
>When you need a LIKE expression or something custom you can do so like this:
>
>->where(
>` t1.name LIKE "` + #somevalue->knop_encodesql_full + `%" `
>::t2.status = 1
>)
>
>Another example would be a MATCH clause:
>
>->where(
>` MATCH (name,keywords) AGAINST ("+sanitised +input" IN BOOLEAN MODE) `
>::t2.status = 1
>)
>
>This is one of the ways that active statements are able to support any SQL
>datasource, they try to avoid any SQL specific to a certain datasource —
>but can handle it via accepting raw string components.
>
>I like the idea of automatic handling of the different types of
>expressions, although time would need to be taken in terms of the syntax
>and ensuring cross compatibility.
>
>Ke
>
>On Mon, Jul 13, 2015 at 4:46 PM Steve Piercy - Website Builder <
>[hidden email]> wrote:
>
>>Thank you for these examples.  Active statements are shweet.
>>
>>Expanding on the discussion, regarding encoding, I've been using
>>->encodesql and ->knop_encodesql_full before passing values into
>>manually constructed SQL statements.
>>
>>Does ds handle that kind of encoding?  I didn't find it in the
>>docs, although I found something in the source I don't fully comprehend.
>>https://github.com/zeroloop/ds/blob/master/statement.lasso#L101-L120
>>
>>One more question, does ds have something that can encode to !=
>>or <> operators?  e.g.,
>>
>>-> where(::moose .ne. 'hair')
>>
>>=>
>>WHERE `moose` <> 'hair'
>>
>>--steve
>>
>
>#############################################################
>
>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]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
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: ds -> join example

Jolle Carlestam-2
13 jul 2015 kl. 08:47 skrev Steve Piercy - Website Builder <[hidden email]>:
>
> * I assume that ->sql("mystring of SQL") is passed through raw
> and not encoded, correct?  I've been using ->encodesql and
> ->knop_encodesql_full in ds for these cases.

Correct, if you provide sql you’re in charge of proper sql encoding.

> * Is there any encoding of values in ds whatsoever?  It looks
> like ds encodes single quotes.

Yes, values are encoded according to their type. Strings are encoded using -> sql_encode. Integers and decimals are kept as their respective type etc.

> user_ds -> where(::username = "bullwinkle'; DROP TABLE
> mysql.user_gone; -- everything after the double-hyphen is
> commented out") -> statement
>
> =>
> SELECT * FROM users WHERE username = 'bullwinkle\'; DROP TABLE
> mysql.user_gone; -- everything after the double-hyphen is
> commented out'

This is perfectly safe. Since the single quote is escaped the entire string is provided as the search value. That is; username need to match the string "bullwinkle\'; DROP TABLE mysql.user_gone; -- everything after the double-hyphen is commented out”
Not very likely to find a match on that… :-)

HDB
Jolle

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

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: ds -> join example

Ke Carlton-3
What Jolle said.

On Mon, Jul 13, 2015 at 6:55 PM Jolle Carlestam <[hidden email]> wrote:

> 13 jul 2015 kl. 08:47 skrev Steve Piercy - Website Builder
> <[hidden email]>:
> >
> > * I assume that ->sql("mystring of SQL") is passed through raw
> > and not encoded, correct?  I've been using ->encodesql and
> > ->knop_encodesql_full in ds for these cases.
>
> Correct, if you provide sql you’re in charge of proper sql encoding.
>
> > * Is there any encoding of values in ds whatsoever?  It looks
> > like ds encodes single quotes.
>
> Yes, values are encoded according to their type. Strings are encoded using
> -> sql_encode. Integers and decimals are kept as their respective type etc.
>
> > user_ds -> where(::username = "bullwinkle'; DROP TABLE
> > mysql.user_gone; -- everything after the double-hyphen is
> > commented out") -> statement
> >
> > =>
> > SELECT * FROM users WHERE username = 'bullwinkle\'; DROP TABLE
> > mysql.user_gone; -- everything after the double-hyphen is
> > commented out'
>
> This is perfectly safe. Since the single quote is escaped the entire
> string is provided as the search value. That is; username need to match the
> string "bullwinkle\'; DROP TABLE mysql.user_gone; -- everything after the
> double-hyphen is commented out”
> Not very likely to find a match on that… :-)
>
> HDB
> Jolle
>
> #############################################################
>
> 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: ds -> join example

stevepiercy
In reply to this post by Jolle Carlestam-2
Got it.  Thanks for confirming, Jolle and Ke, that I've been doing it correctly.

--steve


On 7/13/15 at 8:55 AM, [hidden email] (Jolle Carlestam) pronounced:

> 13 jul 2015 kl. 08:47 skrev Steve Piercy - Website Builder <[hidden email]>:
> >
> > * I assume that ->sql("mystring of SQL") is passed through raw
> > and not encoded, correct?  I've been using ->encodesql and
> > ->knop_encodesql_full in ds for these cases.
>
> Correct, if you provide sql you’re in charge of proper sql encoding.
>
> > * Is there any encoding of values in ds whatsoever?  It looks
> > like ds encodes single quotes.
>
> Yes, values are encoded according to their type. Strings are encoded using -> sql_encode.
> Integers and decimals are kept as their respective type etc.
>
> > user_ds -> where(::username = "bullwinkle'; DROP TABLE
> > mysql.user_gone; -- everything after the double-hyphen is
> > commented out") -> statement
> >
> > =>
> > SELECT * FROM users WHERE username = 'bullwinkle\'; DROP TABLE
> > mysql.user_gone; -- everything after the double-hyphen is
> > commented out'
>
> This is perfectly safe. Since the single quote is escaped the entire string is provided as
> the search value. That is; username need to match the string "bullwinkle\'; DROP TABLE
> mysql.user_gone; -- everything after the double-hyphen is commented out”
> Not very likely to find a match on that… :-)
>
> HDB
> Jolle
>
> #############################################################
>
> 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]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
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: ds -> join example

Jolle Carlestam-2
13 jul 2015 kl. 09:01 skrev Steve Piercy - Website Builder <[hidden email]>:
>
> Got it.  Thanks for confirming, Jolle and Ke, that I've been doing it correctly.

I’m going to stress this once more, possibly repeating myself.

The trap is not when you’re dealing with obvious sql snippets. I’m sure we’re all aware of that if we write -ds -> sql(”My sql query in here”) it needs to be properly sql encoded.
Instead the pitfall could be that we don’t realize that we’re providing DS with sql snippets in other places as well. This discussion has focused on ds -> where. Explaining that when we supply pairs, the value will be sql encoded for us by DS. But when we supply a string it is treated as raw sql and we need to do the proper sql encoding ourselves.

What we also have to understand is that we are providing raw sql in other cases as well. Like when we use a ds -> join or a ds -> from. The params we provide to these methods are also treated as raw sql by DS and thus not protected by any encoding. Situations where we provide user provided input to a ds -> from method is probably rare, but not unprobable. If we do, they have to be protected from sql injections as well.

HDB
Jolle

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

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]>