Input Validation based on entries in MySQL Table? (php / ajax / html / mysql)

Question

I have a simple web-based database using php/mysql that I use to keep track of products leaving my stockroom.

The MySQL database has a bunch of tables but the two I'm concerned with are 'Requests' and 'Salesperson' which you can see below (I've omitted irrelevant information).

Requests
R_ID    ...     R_Salesperson
1       ...     James
2       ...     Bob
3       ...     Craig

Salesperson
S_ID            S_Name
1       ...     James
2       ...     Bob
3       ...     Craig

In my head section I have the following script that dynamically populates a list of our sales staff names as you type them:

// Autocomplete Salesperson Field
$("#form_specialist").autocomplete("../includes/get_salesperson_list.php", {
    width: 260,
    matchContains: true,
    //mustMatch: true,
    //minChars: 0,
    //multiple: true,
    //highlight: false,
    //multipleSeparator: ",",
    selectFirst: false
});

aaand get_salesperson_list.php:

<?php
require_once "get_config.php";
$q = strtolower($_GET["q"]);
if (!$q) return;

$sql = "select DISTINCT S_Name as S_Name from Salesperson where S_Name LIKE '%$q%'";
$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd)) {
$cname = $rs['S_Name'];
echo "$cname\n";
}
?>

I also have some basic javascript input validation requiring a value be entered in the Salesperson field (script is in the head section):

<!-- Input Validation -->
<script language="JavaScript" type="text/javascript">
<!--
function checkform ( form )
{


// ** Validate Salesperson Entry **
if (form.form_specialist.value == "") {
 alert( "Please enter Salesperson Name" );
 form.form_salesperson.focus();
 return false ;
}
// ** END Salesperson Validation **

return true ;
}
//-->
</script>

Aaaaanyway - the problem is I can't figure out how to reject any names not in the 'Salesperson' table. For example - if I were to type 'Jaaames' although it would initially suggest 'James' if I were to ignore it and submit 'Jaaames' this would be entered into the 'Requests' table. This is relatively annoying given my undiagnosed OCD and I'd rather not have to go through hundreds of requests every so often editing them.

Adrian J. Moreno · Accepted Answer · 2011-07-29 14:48:21Z

1

I'd say you're taking the wrong approach here.

The Requests table should NOT be storing the salesperson's NAME, it should be saving their ID. The Primary Key of the Sales Person table.

Then, instead of using auto-complete to populate a TEXT input, I'd recommend using the same approach to populate a SELECT menu that uses the Sales Person's ID as a value.

This accomplishes the following:

your database becomes more normalized
it removes redundant information from the Requests table
removes the need to validate the Sales Person's name on the client side
By defining the S_ID as a foreign key to the Requests table, you ensure that ONLY entries in the Sales Person table can exist in the Requests table.

answered Jul 29, 2011 at 14:48

Adrian J. Moreno

14.9k1 gold badge41 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

totallyNotLizards · Accepted Answer · 2011-07-29 14:42:57Z

You could try binding an AJAX request to either the submit of the form or on changing your text field or maybe when the field loses focus. For this example I am using jQuery:

$('input[name=salesperson').blur(function(){
    //when the text field looses focus
    var n = $(this).val();
    $.post('a_php_file_that_checks_db_for_names.php', {salesperson:n}, function(data){
        //post the name to a php file which in turn looks that name up in the database
        //and returns 1 or 0
        if (data)
        {
           if (data==='1')
           {
               alert('name is in database');
           }
           else
           { 
               alert('name is not in database');
           }
        }
        else 
        {
           alert('no answer from php file');
        }
    });
});

You would also need a PHP file for this to talk to, an example being:

if (isset($_POST['salesperson']))
{
  //query here to check for $_POST['salesperson'] in the db, 
  //fill in the blanks :)
  $yourquery='select name from db where name=?';

  if ($yourquery)
  {
      //looks like there were results, so your name is in the db
      echo '1';
  }
  else
  {
      echo '2';
  } 

}

A bit of filling in the blanks required but you get the idea. Hope this helps you out

EDIT:

A second, more elegant solution just came to mind - if you could get the list of salespersons and make a hidden form field for each, you could read them all into a JS object and test against it whenever the form field is changed. Unfortunately I don't have the time to write you an example but it sounds like a nicer way of doing it to me.

Chris · Accepted Answer · 2011-07-29 14:47:37Z

1

It seems like you're just using Javascript to validate your input - this isn't good as it will never run if your user doesn't support or disables Javascript. As suggested above, a server side validation would be much easier to check against the database. However, client-side validation is also helpful to have as a sort of first line of defense against bad input, since it's generally faster. I can't think of a great way to do this, but one way could be to populate a PHP array of salespersons, convert it to a javascript array, and then check to see if the form value is in the array. It's probably faster (and substantially less code) to just use server-side validation here.

answered Jul 29, 2011 at 14:47

Chris

111 bronze badge

1 Comment

Barry Over a year ago

Fortunately this web-app is only run on an internal network. It is typically only running on a handful of machines - all of which allow javascript :)

user827080 · Accepted Answer · 2011-07-29 14:30:39Z

0

Try adding some sort of validation before you put it on your database? I mean, inside the script that puts the request into the table?

answered Jul 29, 2011 at 14:30

user827080

Comments

curtisdf · Accepted Answer · 2011-07-29 21:01:15Z

-1

The mustMatch option isn't working for you? I see it commented out.

Also, your script is vulnerable to a SQL injection attack. I realize this is an in-house application, but you never know when crazy is going to show up and ruin your day. At the top of your get_salesperson_list.php, right after you retrieve the query from $_GET, you could add something like this:

if (!preg_match("/^\w+$/", $q)) {
    // some kind of error handling here, or at least a refusal to fulfill the request:
    exit;
}

UPDATE: Sorry, I meant to say "exit" instead of "return". I do see that your script wasn't in a function. I have edited the above to account for that. Thanks for pointing that out.

edited Jul 29, 2011 at 21:01

answered Jul 29, 2011 at 14:43

curtisdf

4,2604 gold badges36 silver badges42 bronze badges

2 Comments

Alix Axel Over a year ago

-1, Geez, what has SQL injection got to do with a in-house application? Always use prepared statements or the appropriate functions (like mysql_real_escape_string) to avoid SQL injections. Also, get_salesperson_list.php doesn't seem to be a function so using return won't terminate its execution flow.

Barry Over a year ago

Thanks! - I feel like such an idiot for not noticing I had commented out the function.. Ahhh well - works now :D

Collectives™ on Stack Overflow

Input Validation based on entries in MySQL Table? (php / ajax / html / mysql)

5 Answers 5

Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related