acts_as_list crashes with non-numeric id

[Greg Willits]

Seems to me acts_as_list has a bug -- I have data where the row id is a
random alphaNumeric, not a simple integer. Using acts_as_list with a
scope of a related model id, acts_as_list crashes the app due to a
faulty query in MySQL something like this:

class LineItem < ActiveRecord::Base
belongs_to :order
acts_as_list :scope => :order_id
end

Unknown column 'UXPzIdeIuIFkz6n' in 'where clause': UPDATE `line_items`
SET position = (position - 1) WHERE (order_id = UXPzIdeIuIFkz6n AND
position > 5)

I've been trying several ways to force substition to generate those
needed quotes myself, but so far no luck.

Anyone battle & solve this? Thx.

--
Posted via http://www.ruby-forum.com/.

[Robert Walker]

Greg Willits wrote in post #1109539:

For future reference this is one of those times that fighting Rails
conventions makes your life more difficult as a Rails developer. Rails
wants it simple incrementing integer primary keys. If you can
accommodate that wish then life for you as a developer gets simpler. If
you want a key that is non-numeric, create a separate column and put a
unique constraint on it, but let Rails have its standard simple integer
key for use in creating associations.

If you really want to fix the problem the I suggest you fork the
acts_as_list repository and fix the bug there. My guess is that the
author of acts_as_list assumed the Rails conventions and never tested
the scenario you've presented here.

[Frederick Cheung]

On Monday, May 20, 2013 2:19:28 PM UTC+1, Ruby-Forum.com User wrote:

Greg Willits wrote in post #1109539:
> Seems to me acts_as_list has a bug -- I have data where the row id is a
> random alphaNumeric, not a simple integer. Using acts_as_list with a
> scope of a related model id, acts_as_list crashes the app due to a
> faulty query in MySQL something like this:
>
> class LineItem < ActiveRecord::Base
> belongs_to :order
> acts_as_list :scope => :order_id
> end
>
> Unknown column 'UXPzIdeIuIFkz6n' in 'where clause': UPDATE `line_items`
> SET position = (position - 1) WHERE (order_id = UXPzIdeIuIFkz6n AND
> position > 5)
>
> I've been trying several ways to force substition to generate those
> needed quotes myself, but so far no luck.
>
> Anyone battle & solve this? Thx.

For future reference this is one of those times that fighting Rails
conventions makes your life more difficult as a Rails developer. Rails
wants it simple incrementing integer primary keys. If you can
accommodate that wish then life for you as a developer gets simpler. If
you want a key that is non-numeric, create a separate column and put a
unique constraint on it, but let Rails have its standard simple integer
key for use in creating associations.

This feels like it should work though - scope is not necessarily a foreign key column (it could easily be a status column for example (open/closed/etc)), so it should work with string valued columns. Also anything which allows an unquoted, user controllable string into an SQL query is  a potential security problem

Fred

[Greg Willits]

Robert Walker wrote in post #1109609:

> For future reference this is one of those times that fighting Rails
> conventions makes your life more difficult as a Rails developer.

I've done quite a few apps with non-numeric IDs w/o problems until now.
Apparently I never needed to scope a list (?) or the originall DHH one
didn't have this problem maybe?

> If you really want to fix the problem the I suggest you fork the

> acts_as_list repository and fix the bug there...

hmm...
https://github.com/swanandp/acts_as_list/pull/69

So, awareness, but no fix yet.


Frederick Cheung wrote in post #1109627:

> This feels like it should work though - scope is not necessarily a
> foreign
> key column (it could easily be a status column for example
> (open/closed/etc)), so it should work with string valued columns. Also
> anything which allows an unquoted, user controllable string into an SQL
> query is a potential security problem

That's what I was thinking. Though my (probably incomplete) efforts to
inject some quotes have failed.

Anyway, I guess I'll hack at my local copy and see what I come up
with...

Thanks to both

-- gw

[mike]

The position column in the mapped table needs to be an integer.  See:

github.com/rails/acts_as_list

 

[Greg Willits]

mike wrote in post #1109708:

> On Monday, May 20, 2013 11:19:40 PM UTC-4, Ruby-Forum.com User wrote:
>> > If you really want to fix the problem the I suggest you fork the

>> > foreign

>>
>> Thanks to both
>>
>> -- gw
>>
>> --
>> Posted via http://www.ruby-forum.com/.
>>
>
> The position column in the mapped table needs to be an integer. See:
>
> github.com/rails/acts_as_list

Thx, but we're not talking about the position column.

(and that link is to a much older version)

[Greg Willits]

Greg Willits wrote in post #1109539:

> Seems to me acts_as_list has a bug -- I have data where the row id is a
> random alphaNumeric, not a simple integer. Using acts_as_list with a
> scope of a related model id, acts_as_list crashes the app due to a
> faulty query in MySQL something like this:
>

> Unknown column 'UXPzIdeIuIFkz6n' in 'where clause': UPDATE `line_items`
> SET position = (position - 1) WHERE (order_id = UXPzIdeIuIFkz6n AND
> position > 5)

Doh!! Gemfile did not have a specified version for acts_as_list and was
using an older version. Version 0.2.0 appears to be working fine. With a
simple scope, adding/deleting are working as I need them too.

-- gw