Check if input string conforms with DB collation

ruby-talk
1

What we have is a table with an email field - collation
*latin1_swedish_ci* because
of the limitations of our email service.

···

+----------------+------------+-------------------+------+

Field | Type | Collation | Null |

+----------------+------------+-------------------+------+

id | int(11) | NULL | NO |
email | text | latin1_swedish_ci | YES |
moved_to_users | tinyint(1) | NULL | YES |
created_at | datetime | NULL | YES |
updated_at | datetime | NULL | YES |

+----------------+------------+-------------------+------+

This table is checked when a new user arrives to see if he was already
subscribed.

Comes along a user with characters in their email that would not be valid
for this particular collation. Take for instance *hsıasdf@test.com
<asdf@test.com>*

select * from user_subscriptions where email='hsıasdf@test.com';

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation '='

Would it be possible to determine before saving if a string will adhere to
the collation rules of database?

I tried using transactions as mentioned in this SO answer
<http://stackoverflow.com/a/37409558/2651076> but it does not work or maybe
I am not using it right.

I wrapped this in a begin rescue block as a validation method

def validate_collation_again
  email = params[:name].try(:strip)
  begin
    UserSubscription.find_by_email!(email)
  rescue Exception => e
    #handle
  end
end

Is it possible for me to check before any DB trigger to save/create/find;
if an entry with this particular email will agree with the collation? Some
regex maybe?
String#encode cannot be checked as the application is built using Ruby 1.8.7

This question is also on StackOverflow
<http://stackoverflow.com/questions/37407604/check-if-input-string-conforms-with-db-collation>
and RoR Talk
<https://groups.google.com/d/msg/rubyonrails-talk/8kAIhX_x4Pk/ZFfjQADjGAAJ>.

Regards,
Saurav Kothari

2

Seems like a regexp is your best option. Maybe something like this?

if query_arg =~ /^[\w\d`~!@#$%^&*()_=|}'";:?><.,{\\\[\]\s*+-]+$/
  # ... do query
end

···

On Wed, May 25, 2016 at 4:09 AM, Saurav Kothari <sauravkothari2@gmail.com> wrote:

What we have is a table with an email field - collation
*latin1_swedish_ci* because of the limitations of our email service.

+----------------+------------+-------------------+------+
> Field | Type | Collation | Null |
+----------------+------------+-------------------+------+
> id | int(11) | NULL | NO |
> email | text | latin1_swedish_ci | YES |
> moved_to_users | tinyint(1) | NULL | YES |
> created_at | datetime | NULL | YES |
> updated_at | datetime | NULL | YES |
+----------------+------------+-------------------+------+

This table is checked when a new user arrives to see if he was already
subscribed.

Comes along a user with characters in their email that would not be valid
for this particular collation. Take for instance *hsıasdf@test.com
<asdf@test.com>*

> select * from user_subscriptions where email='hsıasdf@test.com';
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation '='

Would it be possible to determine before saving if a string will adhere
to the collation rules of database?

I tried using transactions as mentioned in this SO answer
<http://stackoverflow.com/a/37409558/2651076> but it does not work or
maybe I am not using it right.

I wrapped this in a begin rescue block as a validation method

def validate_collation_again
  email = params[:name].try(:strip)
  begin
    UserSubscription.find_by_email!(email)
  rescue Exception => e
    #handle
  end
end

Is it possible for me to check before any DB trigger to save/create/find;
if an entry with this particular email will agree with the collation?
Some regex maybe?
String#encode cannot be checked as the application is built using Ruby
1.8.7

This question is also on StackOverflow
<http://stackoverflow.com/questions/37407604/check-if-input-string-conforms-with-db-collation>
and RoR Talk
<https://groups.google.com/d/msg/rubyonrails-talk/8kAIhX_x4Pk/ZFfjQADjGAAJ>
.

Regards,
Saurav Kothari

Unsubscribe: <mailto:ruby-talk-request@ruby-lang.org?subject=unsubscribe>
<http://lists.ruby-lang.org/cgi-bin/mailman/options/ruby-talk>