Trying to insert into mysql database ignoring blank fields

Question

Im taking data from a HTML form and inserting into mysql database. Not all fields are mandatory and so Im trying to ignore those fields if they are empty.

Im have two problems. The first to find a way to deal with the blank fields and the second actually wrting to the database.

Here's what Ive done so far:

    //check if fields are empty
    $insertStrSet = '';
    if($submitted_telephone !== '') {$insertStrSet .= 'telephone=\'$submitted_telephone\',';}
    if($submitted_address !== '') {$insertStrSet .= 'address=\'$submitted_address\',';}
    if($has_image !== ''){ $insertStrSet .= 'has_image=\'$has_image\',';}
    if($final !== ''){ $insertStrSet .= 'image_id=\'$final\',';}    
    $insertStrSet = substr($insertStrSet,0,-1);

    //create sql query
    $insert = "INSERT INTO directory_listings cat_id='$submitted_category', user_id='$user_id', site_name='$submitted_name', description='$submitted_description', url='$submitted_url', country='$submitted_country', email='$submitted_email', $insertStrSet";

mysql_query($insert);

You are using a _deprecated_ extension, have a gaping injection vulnerability and your query is wrong (`INSERT INTO tbl_name (field1, field2, field3) VALUES(1,2,3);`), or if needs must: `INSERT INTO tbl_name SET field = val` <-- note the `SET`! [cf the documentation](http://dev.mysql.com/doc/refman/5.6/en/insert.html) — Elias Van Ootegem, Mar 11 '14 at 15:18
actually he could use that format with SET. INSERT INTO table SET fieldname = value, fieldname2=value2,... — Volkan Ulukut, Mar 11 '14 at 15:24
the variables all all sanitised to avoid injection risk, just omitted here to focus on my problem. sorry for not posting. — Ruf1, Mar 11 '14 at 15:36
Your query should read `INSERT INTO directory_listings (field1, field2, ..., fieldN) values (value1, value2, ..., valueN)`. And you should really favor the `mysqli` (object) extensions to interact with MySQL over its procedural and deprecated counterpart. Look into mysqli and prepared statements where you bind your parameters (http://php.net/manual/en/mysqli.prepare.php). You'll find it rewarding and it'll be safer and more efficient. — VH-NZZ, Mar 11 '14 at 15:37
@Ruf1: sanitized to avoid injection? I honestly doubt that manually sanitizing those values will work in all cases... have you considered multi-byte chars, to name 1 issue with manual sanitation that is commonly overlooked. Besides: really, just go for prepared statements, which means switching to an extension that _is not flagged for removal from PHP!_ — Elias Van Ootegem, Mar 12 '14 at 08:37

score 0 · Accepted Answer · answered Mar 11 '14 at 15:19

0

You can check with empty() function. and you have some syntax error on your sql query. SET is missing. and commas are cosntructed falsely. fixed answer:

//check if fields are empty
$insertStrSet = '';
if(!empty($submitted_telephone)) {$insertStrSet .= 'telephone=\'$submitted_telephone\',';}
if(!empty($submitted_address)) {$insertStrSet .= ', address=\'$submitted_address\'';}
if(!empty($has_image)){ $insertStrSet .= ', has_image=\'$has_image\'';}
if(!empty($final)){ $insertStrSet .= ', image_id=\'$final\'';}    

//create sql query
$insert = "INSERT INTO directory_listings SET cat_id='$submitted_category', user_id='$user_id', site_name='$submitted_name', description='$submitted_description', url='$submitted_url', country='$submitted_country', email='$submitted_email' $insertStrSet";

mysql_query($insert);

answered Mar 11 '14 at 15:19

Volkan Ulukut

4,230
1
20
38

if `$has_image`'s value happens to be `false` or `0`, `empty` will return true, and you won't insert the proper value. Also _please_ mention injection risks OP is subjecting himself to, and explain how `mysql_*` is a bad choice all 'round – Elias Van Ootegem Mar 11 '14 at 15:21
I tried this code thank you but it didnt work. I edited my original code to add SET to sql and that wrote to database but for $insertSetStr variables it entered name and not their value. Any ideas? – Ruf1 Mar 11 '14 at 15:35
do you set $_POST variables to appropriate php variables before this code? like `$submitted_telephone = addslashes($_POST['submitted_telephone ']);` – Volkan Ulukut Mar 11 '14 at 15:38
yes exactly like that. I echo'd the variables and they have values, yet when written to database they are just the variable names like $submitted_telephone – Ruf1 Mar 11 '14 at 15:44
well @EliasVanOotegem you don't know how OP is handling $_POST variables to assume he is exposing himself to injection. also you can always post a new answer or edit mine to improve the answer quality. I don't have to know or care about the difference of mysql and mysqli, which has nothing to do with what is asked here. frankly i'm kinda pissed of **those** guys who wrote in every goddamn question about mysql, **mysqli** and **sql injection**. seriously. giving just a friendly reminder in comment section suffice. you don't have to spam all the answers. – Volkan Ulukut Mar 11 '14 at 16:11
@Ruf1 can you echo $insert and paste here? – Volkan Ulukut Mar 11 '14 at 16:11
1

@VolkanUlukut: Perhaps, however, seeing how the OP intends to interact with a RDBMs, it may be beneficial for him to be made aware of other, more accepted alternatives. As a side note, I believe that building SQL statements by concatenating strings literals should be a federal offense as of 2014. – VH-NZZ Mar 11 '14 at 16:37
Sorry for delay. Site went down. I seem to have fixed it by adopting the following format. 'telephone=\''.$submitted_telephone.'\',';} Given my inexperience im not entirely sure why this worked. On a side note, is better to user !empty, !isset or the if !=='' to see if there's nothing in the variable. – Ruf1 Mar 11 '14 at 17:15

Trying to insert into mysql database ignoring blank fields

1 Answers1

Linked