title: MySQL Case Sensitivity (or otherwise, how to store passwords correctly in MySQL)
body: <p><strong>CAUSE:</strong></p>
  
  <p>I have a table and the columns are all suitably <code>Collated</code> as <code>utf8mb4_unicode_ci</code>, </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `users` (
    `user_id` int(8) NOT NULL AUTO_INCREMENT,
    `username` varchar(100) NOT NULL,
    `pass_word` varchar(512) NOT NULL ,
    ...etc etc...
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `email_addr` (`email_addr`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=989 ;
  </code></pre>
  
  <p>...Including the column storing the password hash (generated from <code>password_hash</code>) such as <code>$2y$14$tFpExwd2TXm43Bd20P4nkMbL1XKxwF.VCpL.FXeVRaUO3FFxGJ4Di</code>. </p>
  
  <p><strong><em>BUT</em></strong>, I find that due to the case insensitivity of the column, that a hash of <code>$2y$14$tFpExwd2tXm43Bd20P4NKmbL1XKxwF.VCpL.FxEVRaUO3FFxGJ4DI</code> would still allow access. </p>
  
  <p>This means that there are potentially hundreds of collisions possible by storing the data in a case insensitive manner. Not good.</p>
  
  <hr>
  
  <p><strong>ISSUE:</strong></p>
  
  <p>Now, Is there a way of forcing MySQL to treat <code>pass_word</code> column as a <strong>case sensitive</strong> column, when doing comparisons. I want to avoid having to edit every occurance of the PHP/SQL querying, and instead simply set the database table column to compare in a case sensitive manner <em>by default</em>.</p>
  
  <p>The <code>utf8mb4</code> character set does not give me any <code>_cs</code> options, and the only non-<code>_ci</code> option appears to be <code>utf8mb4_bin</code>. </p>
  
  <p>So simple questions:</p>
  
  <ul>
  <li><s>Does the <code>UTF8mb4_bin</code> character set &amp; collation on MySQL treat standard comparisons case sensitively?</s>  [yes]</li>
  <li>Dose the <code>UTF8mb4_bin</code> suit what I want to do. Should I use another set, and if so, why? </li>
  <li>Are there any issues in storing <code>password_hash</code> outputs in a MySQL <code>utf8mb4_bin</code> column?</li>
  <li>Does this approach conveniently sidestep the need to edit the query SQL of each login query? Can I change the column type and then move on?</li>
  </ul>
  
  <p><strong>EDIT</strong></p>
  
  <blockquote>
    <p>As detailed by nj_ , this is a silly issue that is not an issue at all because the value of <code>pass_word</code> is never directly edited when logging in.
    ... It's been a long day. </p>
  </blockquote>