Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm not super familiar with PDO, but there is a distinction between "prepared statements" and escaped strings. Escaping is about <strong>removing disallowed character strings</strong> from the query, but prepared statements are about <strong>telling the database what kind of query to expect</strong>.</p> <h2>A query has multiple parts</h2> <p>Think of it this way: when you give a query to the database, you're telling it several separate things. One thing might be, for example, "I want you to do a select." Another might be "limit it to rows WHERE the user name is the following value."</p> <p>If you build up a query as a string and hand it to the database, it doesn't know about either part until it gets the completed string. You might do this:</p> <pre><code>'SELECT * FROM transactions WHERE username=$username' </code></pre> <p>When it gets that string, it has to parse it and decide "this is a <code>SELECT</code> with a <code>WHERE</code>".</p> <h2>Getting the parts mixed up</h2> <p>Suppose a malicious user inputs their user name as <code>billysmith OR 1=1</code>. If you're not careful, you might put that into your string, resulting in:</p> <pre><code>'SELECT * FROM transactions WHERE username=billysmith OR 1=1' </code></pre> <p>...which would return <strong>all the transactions for all users</strong>, because 1 always equals 1. Whoops, you've been hacked!</p> <p>See what happened? <strong>The database didn't know what parts to expect in your query</strong>, so it just parsed the string. It wasn't surprised that the <code>WHERE</code> had an <code>OR</code>, with two conditions that could satisfy it.</p> <h2>Keeping the parts straight</h2> <p>If only it had known <strong>what to expect</strong>, namely, a <code>SELECT</code> whose <code>WHERE</code> had only one condition, the malicious user couldn't have tricked it.</p> <p>With a prepared statement, you can give it that correct expectation. You you can tell the database "I'm about to send you a <code>SELECT</code>, and it's going to be limited to rows <code>WHERE username =</code> a string that I'm about to give you. That's all - there are no other parts to the query. Are you ready? OK, here comes the string to compare to the username."</p> <p>With that expectation, the database wouldn't be fooled: it would only return rows where the <code>username</code> column contains the actual string 'billysmith OR 1=1.' If nobody has that user name, it would return nothing.</p> <h2>Other benefits of prepared statements</h2> <p>In addition to security benefits, prepared statements have a couple of speed benefits:</p> <ul> <li>They can be reused with different parameters, which should be faster than building a new query from scratch, because the database already knows basically what you're about to ask for. It has already built its "query plan".</li> <li>Some databases (Postgres is one, I think) will start making a query plan as soon as they get the prepared statement - before you've actually sent the parameters to use with it. So you may see a speedup even on the first query.</li> </ul> <p>For another explanation, see Theo's answer <a href="https://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php">here</a>.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhy is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USNathan Long
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    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. COThanks! That's just what I need - the plain English version (i.e. the idiot's guide ;-))
      singulars
      1. PO
      1. USBlissC
    2. COWhile I was researching for reasons to use PDO over mysqli (while using prepared statements), this is a very good explanation of why prepared statements are better. It's not that you can't avoid issues by escaping strings (in theory), it's that this approach is both more reliable and quicker to implement (and possibly to run).
      singulars
      1. PO
      1. USRonLugge
    3. COI have about 20 tabs open trying to understand why prepared statements don't need escaping. This answer has explained it perfectly. Everything just clicked after reading. You should convert this answer into a blog post somewhere explaining prepared statements because not one I have come across has explained it as well.
      singulars
      1. PO
      1. USjoe92
 

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