Adding record on database

Question

I'm experiencing a strange problem with save query, and I'd like to better understand how to solve it.

I have a database with 2 tables, example:

TBL_PERSON
person_id
person_name
person_telephone

TBL_ADDRESS
address_id
address_person_id
address_address
address_city
address_zip

Now, I use a query like this to store records:

$sqlQuery = "INSERT INTO TBL_PERSON ( 
    person_name,
    person_telephone
    ) VALUES (
    '$person_name',
    '$person_telephone'
    )";
$result = MYSQL_QUERY($sqlQuery);

//Get last id
$address_person_id = mysql_insert_id();

$sqlQuery = "INSERT INTO TBL_ADDRESS ( 
    address_person_id,
    address_address,
    address_city,
    address_zip
    ) VALUES (
    '$address_person_id',
    '$address_address',
    '$address_city',
    '$address_zip'
    )";
$result = MYSQL_QUERY($sqlQuery);

Sometimes, no record is added on TBL_ADDRESS. After the user presses Insert, Action Button, Name and Telephone are stored on TBL_PERSON, but not address on TBL_ADDRESS.

Are you checking for possible empty fields? Plus, you may want to put this in lowercase `MYSQL_QUERY` whether it makes a difference or not, I've never tried it. — Funk Forty Niner, Jun 06 '14 at 13:01
check for mysql_error (or even better start using mysqli or PDO). — Bojan Kovacevic, Jun 06 '14 at 13:01
Hard to say. Try adding error reporting to the top of your file(s) `error_reporting(E_ALL); ini_set('display_errors', 1);` see if that yields anything. — Funk Forty Niner, Jun 06 '14 at 13:03
As far as we can see from this code, none of the variables in the insert queries are properly escaped. Content in them like a single quote will break the SQL statement. At a minimum to fix this code, you must do `$address_address = mysql_real_escape_string($address_address);` (for all those variables). But the _real_ fix is to switch to an API supporting prepared statements and parameterized queries. See [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for examples via MySQLi and PDO. — Michael Berkowski, Jun 06 '14 at 13:06
To on @MichaelBerkowski comment, the [mysql api you are using is depreciated](http://www.php.net/manual/en/intro.mysql.php). Before you solve anything else you should switch. — Matt R. Wilson, Jun 06 '14 at 13:08
Michael, yes, i have mysql_real_escape_string already present. I have wrote this query in real time... — user2307958, Jun 06 '14 at 13:09
If all variables are properly escaped and you have a value for `$address_person_id` but this is intermittent, you will need to log errors when it happens. `if (!$result) error_log('Error inserting address: ' . mysql_error());` and monitor your web server's error log. — Michael Berkowski, Jun 06 '14 at 13:12
i meant use mysql_error function in php. or print string that php build that you use for mysql_query and try to execute it manually. — Bojan Kovacevic, Jun 06 '14 at 13:12
Thanks all for time spent. I'm going to check out all suggestions. — user2307958, Jun 06 '14 at 13:23

score 0 · Accepted Answer · edited May 23 '17 at 11:49

Barring the discussion on the use of deprecated an insecure mysql_* functions, I think this is a good opportunity to explain methods of debugging issues like this.

In the replacements for the mysql_* query functions, exceptions are thrown on errors allowing you to wrap the query in a try/catch block and handle it accordingly. In the case of mysql_query(), you will simply get false returned from the function. So to be able to debug and consequently see what is wrong, you need to do something like this (from the PHP manual):

$result = mysql_query('SELECT * FROM myTable');
if (!$result) {
   die('Invalid query: ' . mysql_error());
}

If your query fails, you will see why, which is important in debugging.

As mentioned in the comments, you do not escape any of your values. Aside from the fact that you shouldn't be using these functions at all (see mysqli or PDO), you should at minimum be escaping your values using the mysql_real_escape_string() method:

$value = mysql_real_escape_string($value);

If you follow the above logic, you will see what is causing the issue, and I suspect fix it successfully using proper escaping of values. If it's not the value, you may have an issue with your database schema design such as a column that is not nullable and has no default value, yet you may be passing a null value.

Adding record on database

1 Answers1