Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>inserting to db</h2> <p>When inserting data to a MySQL database you should be either using prepared statements or the proper escape function like <code>mysql_real_escape_string</code>. <code>addslashes</code> has nothing to do with databases and should not be used. <em>Escaping</em> is used as a general term but actually covers a large number of operations. Here it seems two uses of escaping are being talked about:</p> <ol> <li>Escaping dangerous values that could be inserted in to a database</li> <li>Escaping string quotes to avoid broken strings</li> </ol> <p>Most database escaping functions do a lot more than just escape quotes. They escape illegal characters and well as invisible characters like \0 ... this is because depending on the database you are using there are lots of ways of breaking an insert - not just by adding a closing quote.</p> <p>Because someone seems to have missed my comment about mentioning PDO I will mention it again here. It is far better to use PDO or some other database abstraction system along with prepared statments, this is because you no longer have to worry about escaping your values.</p> <h2>outputting / dumping db values</h2> <p>In the mentioned <strong>backup your database</strong> script the original coder is using <code>addslashes</code> as a quick shorthand to make sure the outputted strings in the mysql dump are correctly formatted and wont break on re-insert. It has nothing to do with security.</p> <h2>selecting values from a db</h2> <p>Even if you escape your values on insert to the database, you will need to escape <em>the quotes</em> again when writing that data back in to any kind of export file that utilises strings. This is only because you wish to protect your strings so that they are properly formatted.</p> <p>When inserting escaped data into a database, the 'escape sequences' used will be converted back to their original values. for example:</p> <pre><code>INSERT INTO table SET field = "my \"escaped\" value" </code></pre> <p>Once in the database the value will actually be:</p> <pre><code>my "escaped" value </code></pre> <p>So when you pull it back out of the database you will receive:</p> <pre><code>my "escaped" value </code></pre> <p>So when you need to place this in a formatted string/dump, a dump that will be read back in by a parser, you will need to do some kind of escaping to format it correctly:</p> <pre><code>$value_from_database = 'my "escaped" value'; echo '"' . $value_from_database . '"'; </code></pre> <p>Will produce:</p> <pre><code>"my "escaped" value" </code></pre> <p>Which will break any normal string parser, so you need to do something like:</p> <pre><code>$value_from_database = 'my "escaped" value'; echo '"' . addslashes($value_from_database) . '"'; </code></pre> <p>To produce:</p> <pre><code>"my \"escaped\" value" </code></pre> <p>However, if it were me I'd just target the double quote and escape:</p> <pre><code>$value_from_database = 'my "escaped" value'; echo '"' . str_replace('"', '\\"', $value_from_database) . '"'; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PODo I need to use addslashes() when backing up database?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPebbl
    1. USPebbl
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODo I need to use addslashes() when backing up database?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO`mysql_real_escape_string` isn't really a proper mechanism imho.
      singulars
      1. PO
      1. USPeeHaa
    2. CO@PeeHaa it is an option though. especially for coders who are using `addslashes` and may not have the experience to switch directly to PDO/prepared statments. I'd far rather they were using that in their projects than not, wouldn't you? You will also notice I mention prepared statements first, before mysql_real_escape_string... which is obviously the better method.
      singulars
      1. PO
      1. USPebbl
    3. CO@pebbl Great post thanks, I have change it so sql file outputs like: INSERT INTO `test` (`foo`, `bar`) VALUES (1, '\'single quotes\'\r\n\"double quotes\"\r\n\\back slashes\\\r\n/forward slashes/\r\n');
      singulars
      1. PO
      1. USarbme
 

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