StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
9372070
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2012-02-21T04:13:30.517
FavoriteCount
0
LastActivityDate
2012-02-21T04:13:30.517
LastEditDate
LastEditorUserId
0
OwnerUserId
1222524
ParentId
6399221
PostTypeId
2
Score
2
ViewCount
0
LastEditorDisplayName
text
Body
<p>I'd always use "POST" for user data myself, not "GET", and the following discussion reflects that, but you'll still be able to use about 90% of what I say below either way. So here goes...</p> <p>General rule: Don't "think ahead" when escaping data. Only make the immediately necessary transformation. Canonical example: Don't escape for HTML when doing a database insertion, since you'll end up with, for example, '&' turning into '&amp;amp;' after a couple of round trips.</p> <p>General rule: Use UTF-8 throughout. You'll be thankful for this the first time someone does a copy-paste from an email that has a unicode ellipsis in it. (You'd be surprised how often that happens.) Typical settings needed; this may vary with PHP/MySQL/HTML version:</p> <ul> <li>PHP: php_value default_charset "UTF-8"</li> <li>MySQL: Choose utf8 when creating the database.</li> <li>HTML: <meta http-equiv="Content-type" content="text/html;charset=UTF-8"/></li> </ul> <p>General rule: There are only five places that unsanitized (though properly escaped) user data can go:</p> <ul> <li>The value of a (properly-typed) database field (i.e. a UTF-8 text field or blob field).</li> <li>The value of a PHP variable.</li> <li>The value of a Javascript variable.</li> <li>The value of an HTML form element's 'value' attribute.</li> <li>The content of an HTML element's textNode child.</li> </ul> <p>If you want to put user data in <em>any other</em> place, it must be sanitized. This is beyond the scope of the question, but in a typical case you might use a regular expression to replace anything that isn't an ASCII letter or number with an underscore. In cases where uniqueness matters, like a filename or an HTML 'id' attribute, additional work has to be done to make sure that the sanitized names are unique (e.g. make sure that the clash that happens when'a^b' and 'a&b' are both sanitized to 'a_b' is resolved). A typical solution looks something like:</p> <pre><code>filename = original_filename; while(already_exists(filename)) {count++; filename = original_filename + count;} </code></pre> <p>And, finally, the meat of my response: The specific escape functions to use to move data to and from those Five Special Places where unmodified user data can go:</p> <ul> <li>HTML form value attribute -> PHP $_POST variable: No escaping necessary.</li> <li><p>PHP variable -> database field: PDO prepared statement:</p> <pre><code>$stmt = $db->prepare('insert into roundtrip (id, name) values (NULL, :data)'); $stmt->execute(array('data' => $_POST['name'])); </code></pre></li> <li><p>Database field -> PHP variable: No escaping necessary, but use PDO prepared statement to escape query values:</p> <pre><code>$stmt = $db->prepare('select id, name from roundtrip where name = :data'); $stmt->execute(array('data' => $_POST['name'])); // User data needs escaping. while ($result = $stmt->fetch()) { echo $result['name']; // DB result doesn't. } </code></pre></li> <li><p>PHP variable -> Javascript variable: json_encode:</p> <pre><code>var data = <?php echo json_encode(data); ?>; </code></pre></li> <li><p>PHP variable -> HTML textNode or form value: htmlspecialchars:</p> <pre><code><div><?php echo htmlspecialchars(data); ?></div> <input type="text" name="name" value="<?php echo htmlspecialchars(data); ?>"/> </code></pre></li> <li><p>Javascript <-> HTML textNode or form value: Browser's built-in textNode and .value attributes/functions:</p> <pre><code>data = source_div.textContent; // Firefox data = source_div.innerText; // Other browsers target_div.textContent = data; // Firefox target_div.innerText = data; // Other browsers // To/from form element. data = source_form.value; target_form.value = data; // Append to element. target_div.appendChild(document.createTextNode(data)); // All browsers // jQuery textNode data = $('#source_div_id').text(); $('#target_div_id').text(data); // jQuery form value data = $('#source_form_id').val(); $('#target_form_id').val(data); </code></pre></li> </ul> <p>Do a repeated round-trip test with a string like this to make sure it always goes through the whole HTML->PHP->DB->PHP->[Javascript->]HTML cycle exactly the same every time:</p> <pre><code>&amp;ДЖäüöéè<script>…</script>™<i>bold</i> </code></pre> <p>Here's my script that tests escaping every which way; it obviously needs a database, a table with name 'roundtrip' and columns 'id' and 'name', and single row with id=1 to be created before running it:</p> <pre><code><?php $db = new PDO("mysql:host=$host;dbname=$dbname", $db_user, $db_password); $stmt_insert = $db->prepare(' update roundtrip set name = :name where id = 1 '); $stmt_select = $db->prepare(' select name from roundtrip where id = 1 '); if ($_POST['do'] == 'edit') { $stmt_insert->execute(array('name' => $_POST['name'])); } $stmt_select->execute(); while ($result = $stmt_select->fetch()) { $data = $result['name']; } ?> <!DOCTYPE html> <html> <head> <title>Roundtrip test</title> <script type="text/javascript" src="/js/jquery-1.7.1.min.js"></script> <script type="text/javascript"> function copydiv() { // Non-jquery: //var source = document.getElementById('divdata'); //var target = document.getElementById('copydiv'); //if (typeof(source.textContent) != "undefined") { // target.textContent = source.textContent; //} else { // target.innerText = source.innerText; //} // jquery: $('#copydiv').text($('#divdata').text()); } function copyform() { // Non-jquery: //var source = document.getElementById('formdata'); //var target1 = document.getElementById('copyform1'); //var target2 = document.getElementById('copyform2'); //if (typeof(source.textContent) != "undefined") { // target1.textContent = source.value; //} else { // target1.innerText = source.value; //} //target2.value = source.value; // jquery: $('#copyform1').text($('#formdata').val()); $('#copyform2').val($('#formdata').val()); } function copyjson() { var data = <?php echo json_encode($data); ?>; // Non-jquery: //var target = document.getElementById('copyjson'); //if (typeof(target.textContent) != "undefined") { // target.textContent = data; //} else { // target.innerText = data; //} // jquery: $('#copyjson').text(data); } </script> </head> <body> <div>Data: <span id="divdata"><?php echo htmlspecialchars($data); ?></span></div> <div>JS div copy: <span id="copydiv"/></div> <div>JS form copy: <span id="copyform1"/></div> <div>JSON copy: <span id="copyjson"/></div> <form method="POST"> <input type="hidden" name="do" value="edit"/> <input type="text" name="name" id="formdata" value="<?php echo htmlspecialchars($data); ?>"/> <input type="text" id="copyform2"/> <input type="button" value="Copy div" onclick="copydiv();"/> <input type="button" value="Copy form" onclick="copyform();"/> <input type="button" value="Copy json" onclick="copyjson();"/> <input type="submit"/> </form> </body> </html> </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POBroad overview, flowchart on escaping user submitted content for html, javascript, and PHP
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USAndrew Klaassen
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
2. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId
1. COthanks! a solid simple run down
  singulars
  PostPostId
  PO
  UserUserId
  USweather

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.