how to check data in a form?

Hi, I have a application that is built using PHP and mysql database. On the form there is a field where I enter an account number in the format xxxx-xxxxxxx. Once I enter the account number and press the submit button ( after entering some other fields too). I need a check to be done to make sure that account number does not exist with the record status as 'open' in the db before the new account number and the corresponding data is posted to the database. How can I do tihis? Thanks for the help.
aej1973Asked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
Note regarding these two lines of code...

     $acct_num = $_POST["acct_num"];
     $result = mysql_query("SELECT * from table WHERE account_number = '" . $acct_num ."' AND status = 'Open'");

There is something vitally important missing between these two lines.  That is the part where you sanitize the external input.  All external input is, by definition, tainted and therefore must be considered an attack vector.  

At a minimum you must use this:
http://php.net/manual/en/function.mysql-real-escape-string.php

Learn more here:
http://php.net/manual/en/security.database.sql-injection.php
http://en.wikipedia.org/wiki/SQL_injection

Consider becoming involved with OWASP:
https://www.owasp.org/index.php/Main_Page

0
 
mrh14852Commented:
Without any of your code I will just assume a simple form field and this is a very basic straight forward way to check.  You could build this out much more.

<input type="text" name="acct_num" />
<?php

if(isset($_POST)){
     $acct_num = $_POST["acct_num"];
     
     //connect to mysql using your connection function

     $result = mysql_query("SELECT account_number, status from table WHERE account_number = '" . $acct_num ."' AND status = 'Open'");

     if (mysql_num_rows($result) = 0) {
          // this means that account number does not have anything in the DB with open status
          // insert new info
     }
     else {
          // this means there is an entry in the DB with that account number in open status
          // do something
     }

?>

Open in new window

0
 
aej1973Author Commented:
Thank you. I have two questions:

1) if the acct_num does not exist the app needs to be redirected to some other page , say account_page.php.where the db update function is carried out.

2) If the acct_num exists then the user is directed to another page shows the record details corresponding to that account number.. Each record has a unique ID column.

Thanks for the help.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
mrh14852Commented:
Best way to do all of this is through Javascript and AJAX.  You could submit the form and return the results back to the browser and do something from there.

You can just make the PHP file you post to do the work for you.

<?php

if(isset($_POST)){
     $acct_num = $_POST["acct_num"];
     
     //connect to mysql using your connection function
     
     // Change the query to * or all the field you would want to display.
     $result = mysql_query("SELECT * from table WHERE account_number = '" . $acct_num ."' AND status = 'Open'");

     if (mysql_num_rows($result) = 0) {
          // create your query here with the POST elements and update the DB.

          // You could then echo out a success message or you could echo out the form elements that the user just submitted.

     }
     else {
          // Use the results from the original query to display the account information

          $data = mysql_fetch_assoc($result);
          $status = $data['status'];
          $foo = $data['foo'];
          $bar = $data['bar'];
          etc...

          echo "<table>
                    <tr>
                    <td>{$status}</td>
                    <td>{$foo}</td>
                    <td>{$bar}</td>
                    </tr>
                    </table>";
     }

?>

Open in new window


Or something like that.
0
 
Ray PaseurCommented:
Regarding this:
if the acct_num does not exist the app needs to be redirected to some other page , say account_page.php.where the db update function is carried out.

You will probably find that redirecting to another page is an irrelevant action.  You've already got the database connected and selected in the current script, you've got the data you need in the current script.  Why not just perform the update in this same script?  I think that is what a professional would do.

Good answers to basic questions like this one can be found in this excellent little book from SitePoint.
http://www.sitepoint.com/books/phpmysql4/

Very readable with great examples and now in its fourth printing, it has been a permanent part of my professional library since Edition One.
0
 
mrh14852Commented:
Did these posts help you?
0
 
aej1973Author Commented:
Thank you for all the suggestions, I will be working on it later this evening.
0
 
aej1973Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.