Databases
StackOverflow2013
Postsdbo
POReference: What is a perfect code sample using the MySQL extension?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReference: What is a perfect code sample using the MySQL extension?
    primarykey
    data
    text
    <blockquote> <p>This is to create a <strong>community learning resource</strong>. The goal is to have examples of good code that do not repeat the awful mistakes that can so often be found in copy/pasted PHP code. I have requested it be made Community Wiki. </p> <p>This is <strong>not meant as a coding contest.</strong> It's not about finding the fastest or most compact way to do a query - it's to provide a good, readable reference especially for newbies. </p> </blockquote> <p>Every day, there is a huge influx of questions with <em>really bad</em> code snippets using the <code>mysql_*</code> family of functions on Stack Overflow. While it is usually best to direct those people towards PDO, it sometimes is neither possible (e.g. inherited legacy software) nor a realistic expectation (users are already using it in their project).</p> <p>Common problems with code using the <code>mysql_*</code> library include:</p> <ul> <li>SQL injection in values</li> <li>SQL injection in LIMIT clauses and dynamic table names</li> <li>No error reporting ("Why does this query not work?")</li> <li>Broken error reporting (that is, errors always occur even when the code is put into production)</li> <li>Cross-site scripting (XSS) injection in value output</li> </ul> <p>Let's write a PHP code sample that does the following using the <a href="http://php.net/manual/en/book.mysql.php" rel="noreferrer">mySQL_* family of functions</a>:</p> <ul> <li>Accept two POST values, <code>id</code> (numeric) and <code>name</code> (a string)</li> <li>Do an UPDATE query on a table <code>tablename</code>, changing the <code>name</code> column in the row with the ID <code>id</code></li> <li>On failure, exit graciously, but show the detailed error only in production mode. <code>trigger_error()</code> will suffice; alternatively use a method of your choosing </li> <li>Output the message "<code>$name</code> updated."</li> </ul> <p>And does <strong>not</strong> show any of the weaknesses listed above.</p> <p>It should be <strong>as simple as possible</strong>. It ideally doesn't contain any functions or classes. The goal is not to create a copy/pasteable library, but to <strong>show the minimum of what needs to be done to make database querying safe.</strong></p> <p>Bonus points for good comments.</p> <p>The goal is to make this question a resource that a user can link to when encountering a question asker who has bad code (even though it isn't the focus of the question at all) or is confronted with a failing query and doesn't know how to fix it. </p> <p><strong>To pre-empt PDO discussion:</strong></p> <p>Yes, it will often be preferable to direct the individuals writing those questions to PDO. When it is an option, we should do so. It is, however, not always possible - sometimes, the question asker is working on legacy code, or has already come a long way with this library, and is unlikely to change it now. Also, the <code>mysql_*</code> family of functions is perfectly safe if used properly. So no "use PDO" answers here please.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPeter O.
    1. USPekka 웃
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POReference: What is a perfect code sample using the MySQL extension?
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POReference: What is a perfect code sample using the MySQL extension?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POReference: What is a perfect code sample using the MySQL extension?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload