Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your code does not do any validation, it just <code>die</code>s when there's an error.<br> This will not do in production code. </p> <p>As far as validation goes you have 2 options: </p> <p>A - Check the input in php and disallow any illegal values.<br> B - Use SQL constraints and monitor the output there.<br> Or a combination of the two. </p> <p>The problem with option <strong>B</strong> is that you'll just get an error back from MySQL that you'll have to handle in php.<br> For that reason I'd go for option A. </p> <p><strong>First step: escape your input</strong><br> The best way to do this is to use <a href="http://php.net/manual/en/ref.pdo-mysql.php" rel="nofollow">PDO</a>.<br> If you use the <code>mysql_</code> lib you'll have to use <code>mysql_real_escape_string</code> on each and every input to escape it and quote your <code>$vars</code>; like so:</p> <pre><code>$a = mysql_real_escape_string($_GET['param1']); examplequery = "SELECT * FROM table1 WHERE field1 = '$a' "; </code></pre> <p>For integer values you can cast the variable to an integer, and then inject it without quotes. </p> <pre><code>$a = intval($_GET['param1']); $b = intval($_GET['param2']); if $a &gt; $b { list($a,$b) = array($b,$a); } //Exchange the two vars if needed. examplequery = "SELECT * FROM table1 WHERE field1 BETWEEN $a AND $b"; </code></pre> <p><strong>Validating your data</strong><br> Here again you have two options, you can hardcode the validation into php, or use SQL to check the input.<br> I like the second approach, because it allows you to put all info in valid input into the database. </p> <p><strong>Validating your data using a validation table</strong><br> One option is to create a table like so:</p> <pre><code>table Checks ( fieldname varchar(50) not null, tablename varchar(50) not null, fieldtype enum('int','varchar','enum','decimal'.....) not null, min_value double default -1000000; max_value double default 1000000; validation_query varchar(1000) default null, primary key (tablename, fieldname)) ENGINE = InnoDB; </code></pre> <p>Now you can check a param like so (using PDO):</p> <pre><code>$stmt = $dbh-&gt;prepare("SELECT fieldtype, min_value, max_value, validation_query FROM checks WHERE fieldname = ? AND tablename = ?"); $stmt-&gt;execute(array('field1', 'table1')); $result = $stmt-&gt;fetch(PDO::FETCH_ASSOC); switch ($result['type']) { case 'int': $allOK = isnumeric($inputvalue_to_check); $allOK = $allOK AND ($intval($inputvalue_to_check) &gt;= $result['min_value'] AND $intval($inputvalue_to_check) &lt;= $result['max_value']); if is_null($result['validation_query']) { $sql = $result['validation_query']; $check = $dbh-?prepare($sql); $check_result = $check-&gt;execute(array($inputvalue_to_check)); $check_result-&gt;fetch(PDO::FETCH_ASSOC); if is_null($check_result['result']) { //value is not OK} break; case ....... </code></pre> <p><strong>Validating in php</strong><br> If you know your data and it's not subject to change, you can do the validation using <code>if</code> and <code>switch</code> statements.<br> This code looks a lot like the above code, except that it does not fetch data from the database, the logic is hardcoded instead or is stored in php datastructures. </p>
    singulars
    1. This table or related slice is empty.
    1. POavoiding blank and duplicate entries submitted to sql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJohan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POavoiding blank and duplicate entries submitted to sql
      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
    1. COThank you so much for this level of detail and effort. Will really help me understand validation.
      singulars
      1. PO
      1. USMike Thrussell
 

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