<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="secure-client-programming">
      </a>
      8.1.7 Client Programming Security Guidelines
     </h3>
    </div>
   </div>
  </div>
  <p>
   Client applications that access MySQL should use the following
      guidelines to avoid interpreting external data incorrectly or
      exposing sensitive information.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="secure-client-programming.html#client-external-data-handling" title="Handle External Data Properly">
       Handle External Data Properly
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="secure-client-programming.html#client-mysql-error-handling" title="Handle MySQL Error Messages Properly">
       Handle MySQL Error Messages Properly
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="client-external-data-handling">
       </a>
       Handle External Data Properly
      </h4>
     </div>
    </div>
   </div>
   <p>
    Applications that access MySQL should not trust any data entered
        by users, who can try to trick your code by entering special or
        escaped character sequences in Web forms, URLs, or whatever
        application you have built. Be sure that your application
        remains secure if a user tries to perform SQL injection by
        entering something like
    <code class="literal">
     ; DROP DATABASE
        mysql;
    </code>
    into a form. This is an extreme example, but
        large security leaks and data loss might occur as a result of
        hackers using similar techniques, if you do not prepare for
        them.
   </p>
   <a class="indexterm" name="idm46045252397904">
   </a>
   <a class="indexterm" name="idm46045252396832">
   </a>
   <p>
    A common mistake is to protect only string data values. Remember
        to check numeric data as well. If an application generates a
        query such as
    <code class="literal">
     SELECT * FROM table WHERE
        ID=234
    </code>
    when a user enters the value
    <code class="literal">
     234
    </code>
    , the user can enter the value
    <code class="literal">
     234 OR 1=1
    </code>
    to cause the application to
        generate the query
    <code class="literal">
     SELECT * FROM table WHERE ID=234 OR
        1=1
    </code>
    . As a result, the server retrieves every row in
        the table. This exposes every row and causes excessive server
        load. The simplest way to protect from this type of attack is to
        use single quotation marks around the numeric constants:
    <code class="literal">
     SELECT * FROM table WHERE ID='234'
    </code>
    . If the
        user enters extra information, it all becomes part of the
        string. In a numeric context, MySQL automatically converts this
        string to a number and strips any trailing nonnumeric characters
        from it.
   </p>
   <p>
    Sometimes people think that if a database contains only publicly
        available data, it need not be protected. This is incorrect.
        Even if it is permissible to display any row in the database,
        you should still protect against denial of service attacks (for
        example, those that are based on the technique in the preceding
        paragraph that causes the server to waste resources). Otherwise,
        your server becomes unresponsive to legitimate users.
   </p>
   <p>
    Checklist:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Enable strict SQL mode to tell the server to be more
            restrictive of what data values it accepts. See
       <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
        Section 7.1.11, “Server SQL Modes”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Try to enter single and double quotation marks
            (
       <code class="literal">
        '
       </code>
       and
       <code class="literal">
        "
       </code>
       ) in all of
            your Web forms. If you get any kind of MySQL error,
            investigate the problem right away.
      </p>
     </li>
     <li class="listitem">
      <p>
       Try to modify dynamic URLs by adding
       <code class="literal">
        %22
       </code>
       (
       <code class="literal">
        "
       </code>
       ),
       <code class="literal">
        %23
       </code>
       (
       <code class="literal">
        #
       </code>
       ), and
       <code class="literal">
        %27
       </code>
       (
       <code class="literal">
        '
       </code>
       ) to them.
      </p>
     </li>
     <li class="listitem">
      <p>
       Try to modify data types in dynamic URLs from numeric to
            character types using the characters shown in the previous
            examples. Your application should be safe against these and
            similar attacks.
      </p>
     </li>
     <li class="listitem">
      <p>
       Try to enter characters, spaces, and special symbols rather
            than numbers in numeric fields. Your application should
            remove them before passing them to MySQL or else generate an
            error. Passing unchecked values to MySQL is very dangerous!
      </p>
     </li>
     <li class="listitem">
      <p>
       Check the size of data before passing it to MySQL.
      </p>
     </li>
     <li class="listitem">
      <p>
       Have your application connect to the database using a user
            name different from the one you use for administrative
            purposes. Do not give your applications any access
            privileges they do not need.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Many application programming interfaces provide a means of
        escaping special characters in data values. Properly used, this
        prevents application users from entering values that cause the
        application to generate statements that have a different effect
        than you intend:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       MySQL SQL statements: Use SQL prepared statements and accept
            data values only by means of placeholders; see
       <a class="xref" href="sql-prepared-statements.html" title="15.5 Prepared Statements">
        Section 15.5, “Prepared Statements”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       MySQL C API: Use the
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-escape-string-quote.html" target="_top">
        <code class="literal">
         mysql_real_escape_string_quote()
        </code>
       </a>
       API call. Alternatively, use the C API prepared statement
            interface and accept data values only by means of
            placeholders; see
       <a class="ulink" href="/doc/c-api/8.4/en/c-api-prepared-statement-interface.html" target="_top">
        C API Prepared Statement Interface
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       MySQL++: Use the
       <code class="literal">
        escape
       </code>
       and
       <code class="literal">
        quote
       </code>
       modifiers for query streams.
      </p>
     </li>
     <li class="listitem">
      <p>
       PHP: Use either the
       <code class="literal">
        mysqli
       </code>
       or
       <code class="literal">
        pdo_mysql
       </code>
       extensions, and not the older
       <code class="literal">
        ext/mysql
       </code>
       extension. The preferred API's
            support the improved MySQL authentication protocol and
            passwords, as well as prepared statements with placeholders.
            See also
       <a class="ulink" href="/doc/apis-php/en/" target="_top">
        MySQL and PHP
       </a>
       .
      </p>
      <p>
       If the older
       <code class="literal">
        ext/mysql
       </code>
       extension must be
            used, then for escaping use the
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-escape-string-quote.html" target="_top">
        <code class="literal">
         mysql_real_escape_string_quote()
        </code>
       </a>
       function and not
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-escape-string.html" target="_top">
        <code class="literal">
         mysql_escape_string()
        </code>
       </a>
       or
       <code class="function">
        addslashes()
       </code>
       because only
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-escape-string-quote.html" target="_top">
        <code class="literal">
         mysql_real_escape_string_quote()
        </code>
       </a>
       is character set-aware; the other functions can be
       <span class="quote">
        “
        <span class="quote">
         bypassed
        </span>
        ”
       </span>
       when using (invalid) multibyte
            character sets.
      </p>
     </li>
     <li class="listitem">
      <p>
       Perl DBI: Use placeholders or the
       <code class="literal">
        quote()
       </code>
       method.
      </p>
     </li>
     <li class="listitem">
      <p>
       Java JDBC: Use a
       <code class="literal">
        PreparedStatement
       </code>
       object
            and placeholders.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Other programming interfaces might have similar capabilities.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="client-mysql-error-handling">
       </a>
       Handle MySQL Error Messages Properly
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045252352080">
   </a>
   <a class="indexterm" name="idm46045252350992">
   </a>
   <p>
    It is the application's responsibility to intercept errors that
        occur as a result of executing SQL statements with the MySQL
        database server and handle them appropriately.
   </p>
   <p>
    The information returned in a MySQL error is not gratuitous
        because that information is key in debugging MySQL using
        applications. It would be nearly impossible, for example, to
        debug a common 10-way join
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement without providing information regarding which
        databases, tables, and other objects are involved with problems.
        Thus, MySQL errors must sometimes necessarily contain references
        to the names of those objects.
   </p>
   <p>
    A simple but insecure approach for an application when it
        receives such an error from MySQL is to intercept it and display
        it verbatim to the client. However, revealing error information
        is a known application vulnerability type
        (
    <a class="ulink" href="http://cwe.mitre.org/data/definitions/209.html" target="_blank">
     CWE-209
    </a>
    )
        and the application developer must ensure the application does
        not have this vulnerability.
   </p>
   <p>
    For example, an application that displays a message such as this
        exposes both a database name and a table name to clients, which
        is information a client might attempt to exploit:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa78583715"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist</code></pre>
   </div>
   <p>
    Instead, the proper behavior for an application when it receives
        such an error from MySQL is to log appropriate information,
        including the error information, to a secure audit location only
        accessible to trusted personnel. The application can return
        something more generic such as
    <span class="quote">
     “
     <span class="quote">
      Internal Error
     </span>
     ”
    </span>
    to
        the user.
   </p>
  </div>
 </div>
 <br/>
</div>
