Form is submitting empty values to database

Question

After being advised that i MUST validate my form so that no-one could hack my database i then made some changes which were adding the mysql_real_string()

$query="INSERT INTO allymccoist (id, firstname, lastname, email, date) 
VALUES (NULL, '".$firstname."', '".$lastname."', '".$email."',   '".mysql_real_escape_string($new_date)."')";

$firstname = mysql_real_escape_string($_POST['firstname']);
$lastname = mysql_real_escape_string($_POST['lastname']);
$email = mysql_real_escape_string($_POST['email']);
$datepicker = mysql_real_escape_string($_POST['date']);

since doing this, nothing is being sent to firstname lastname or email although the date seems to be sending ok though

is thereanything that may be causing this that you can see from my code?

Check the values of the variables you are using — make sure that they actually have data in them. And don't play with `mysql_real_escape_string` and string mashing, it makes the code very hard to read. Use [an API that supports parameterization](http://bobby-tables.com/php.html) — Quentin, Aug 06 '11 at 13:17
Is your code in this order? As in, query definition first -> then definitions of $firstname, $lastname? If so, that is your issue. — NSSec, Aug 06 '11 at 13:17

Damien Pirsy · Accepted Answer · 2011-08-06T14:12:50.580

0

If you're sure that those data actually are set (var_dump your $_POST array to check that),then make sure you have a connection active before using mysql_real_escape_string(), as it would return FALSE otherwise:

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used.

So you can well be entering FALSE in every value.

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')or die(mysql_error());
mysql_select_db('database_name', $link) or die('cannot select database '.mysql_error());

$firstname = mysql_real_escape_string($_POST['firstname']);
$lastname = mysql_real_escape_string($_POST['lastname']);
$email = mysql_real_escape_string($_POST['email']);
$datepicker = mysql_real_escape_string($_POST['date']);

You'd be better off altogether by using prepared statements, so you won't have to worry about SQL injections.

Also, I'd advice you against using NULL in your insert query for the field ID. If you're table is strcutred as I can guess, and ID is a primary key with AutoIncrement, you don't need to enter it in your query, as it would be automatically filled by the engine.

For wheter it is better to use prepared statements or mysql_real_escape_string(), check this resource mysql_real_escape_string vs prepared statements

edited Aug 06 '11 at 14:12

answered Aug 06 '11 at 13:17

Damien Pirsy

25,319
8
70
77

so would i only have to do this: $query = $dbh->prepare("INSERT INTO allymccoist (id, firstname, lastname, email, date) VALUES ('".$firstname."', '".$lastname."', '".$email."', '".$new_date."')"); – Gezzamondo Aug 06 '11 at 13:27
I wouldn't advise using prepared statements for something so simple as this query, and I certainly wouldn't advise using it to simply avoid the need to escape params – adlawson Aug 06 '11 at 13:31
as im new to php im a little worried about someone hackin the database with sql injestions... will the method im current using with mysql_real_esape_string() be enough protection?? – Gezzamondo Aug 06 '11 at 13:39
@adlawson well, if he's only doing this in his whole app you're right, but if this just a piece and external supplied data are frequent, prepared statemts would be a better choice. Also, I never said they're good only to avoid escaping. – Damien Pirsy Aug 06 '11 at 13:40
im lost... should i be using escape parameters or mysql_real_escape_string() to prevent from sql injections? – Gezzamondo Aug 06 '11 at 14:00
It depends. Both work, but mysql_real_escape_string() is subject to the character set. I updated my answer with a link to a resource. You can also browse SO for similar question, like http://stackoverflow.com/questions/2353666/php-is-mysql-real-escape-string-sufficient-for-cleaning-user-input – Damien Pirsy Aug 06 '11 at 14:12

score 0 · Answer 2 · answered Aug 06 '11 at 18:37

The issue of missing data is likely as Damien suggests. Establish a connection, then use mysql_real_escape_string(). The connection is required in part so that mysql_real_escape_string() can take into account the current character set of the connection.

Also, mysql_real_escape_string() is perfectly safe when used in combination with the sprintf() function (full details on sprintf). Most important with sprintf() is setting the correct type specifier so that values get cast properly. Generally, for integers you will use %d. For floats use %f. And for string and date values use %s.

So for your program the code should look something like (note: as Damien suggests, leave id out of the query):

   /* Read form data. */
   $firstName = $_POST['firstname'];
   $lastName = $_POST['lastname'];
   $email = $_POST['email'];
   $date = $_POST['date']);

   /* Your form validation code here. */ 

   /* Your db connection code here. */

   /* Setup and run your query. */
   $query = sprintf("INSERT INTO allymccoist (firstname, lastname, email, date) 
            VALUES ('%s', '%s', '%s',   '%s')", 
            mysql_real_escape_string($firstName), 
            mysql_real_escape_string($lastName), 
            mysql_real_escape_string($email), 
            mysql_real_escape_string($date));

    $result = mysql_query($query);

    /* Check for errors with query execution. */
    if (!$result) echo("Query Error! Process aborted.");

Form is submitting empty values to database

2 Answers2