Queries with empty values question

[TeddyKiller]

If one has a form.. with updates the database via userid, if some fields were left empty.. would inserting the values into the query update the collum row to NULL in the database?

If so, how can this be prevented.

 

Cheers

[TeddyKiller]

Just tested, it does. So how could I avoid it overwriting?

[premiso]

Dynamically build the update query and leave out any empty fields. For an example of how to dynamically build a SQL Query from GET data you can take a look at this tutorial:

 

http://www.phpfreaks.com/tutorial/simple-sql-search

[TeddyKiller]

it has..

$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

 

Though still don't see how I'd update the database.

 

HOWEVER. I thought of this, it might be a messy way though.

$fields = array();
if(isset($_POST['myfield'])) { $fields[] = "mycollum='".$_POST['myfield']."'"; }
$fields = implode(', ', $fields);
$query = mysql_query("update table set $fields where id = $userid");

 

This would work.. wouldn't it?

[Ken2k7]

Woah.. syntax errors. That's bad.

 

Try a loop.

 

<?php
$update_str = '';
foreach ($_POST as $key => $value) {
     if (!empty($value)) {
          // do something
     }
}

 

There, I helped you get started.

[TeddyKiller]

Using that, I wouldn't be able to have "$fields[] = "mycollum='".$_POST['myfield']."'";"

because the collum names would have to be different..?

[Ken2k7]

There is a simple way to solve that. Be creative. That's the best thing about programming. You can be as creative as you want.

[TeddyKiller]

I'm not sure.. what you mean? Sorry.

 

Here is my code. (The one that overwrites)

  if(isset($_POST['submit'])){
  $user = check_user($secret_key);
  	  $profile = check_profile();

  	  $proname = stripslashes($_POST['proname']);
  $interestedin = strtolower($_POST['interestedin']);
  $lookingfor = strtolower($_POST['lookingfor']);
  $marstatus = strtolower($_POST['marstatus']);
  $orienstatus = strtolower($_POST['orienstatus']);
  $gender = strtolower($_POST['gender']);
  $likes = stripslashes($_POST['likes']);
  $dislikes = stripslashes($_POST['dislikes']);
  $music = stripslashes($_POST['music']);

  $query = mysql_query("update `profile` set `pro_name`='$proname', `interested_in`='$interestedin', `looking_for`='$lookingfor', `rel_status`='$marstatus', `orien_status`='$orienstatus', `likes`='$likes', `dislikes`='$dislikes', `music`='$music' where `user_id`='$user->id'");
  if($query) {
	  $query = mysql_query("update `users` set `gender`='$gender' where `id`='$user->id'");
	  if($query) {
		  echo 'Profile updated!';
	  }
  }
  }

[premiso]

Given that you want to use fields:

 

      // grab the form fields.
      $fields = array();
      $fields[] = isset($_POST['proname'])?"`proname` = '{$_POST['proname']}'":'';
      $fields[] = isset($_POST'interestedin'])?"`interestedin` = '{$_POST['interestedin']}'":'';
      $fields[] = isset($_POST['lookingfor'])?"`lookingfor` = '{$_POST['lookingfor']}'":'';

      foreach ($fields as $key => $field) {
            if (empty($field)) {
                   unset($fields[$key]);
            }
      }

     mysql_query("UPDATE profile SET " . implode(", ", $fields) . " WHERE where `user_id`='$user->id'") or trigger_error("Update Failed: " . mysql_error());

 

Should give you a better idea what is going on. And for reference the ? and : make up the ternary operator which is just a shortened if / else statement.  (if some condition) ? do this : else this;

 

Given that you do not care what gets lowerecased, I added another statement in the foreach to remove empties to escape the data (As it should be escaped to prevent SQL injection) and lowercase the variable. If magic_quotes are on, it will stripslashes before escaping it to avoid dupping the slashes.

 

Removed that part because, yea I was just being an idiot. You should look into mysql_real_escape_string and use it with get_magic_quotes_gpc to verify if you need to stripslashes before you escape to prevent SQL Injection

 

Hope that helps ya.

[premiso]

Eh hit quote instead of modify. Fixed in previous post.

[TeddyKiller]

Thank you. I see what you have done. The piece of code you removed, completely confused my brain.

I'm only wanting strtolower for certain fields. Would I do this?

$fields[] = strtolower(isset($_POST['interestedin'])?"`interestedin` = '{$_POST['interestedin']}'":'');

Or ..

$fields[] = isset($_POST['interestedin'])?"`interestedin` = '{strtolower($_POST['interestedin'])}'":'';

 

I'm rather confused. I know about mysql_real_escape_string() 's but only use it for .. registration forms, or information that should be kept secure.

 

EDIT: Ouch.. copying code from here, and putting it on here doesn't like colours

[premiso]

Neither.

 

$fields[] = isset($_POST['interestedin'])?"`interestedin` = '" . strtolower($_POST['interestedin']) . "'":'';

 

Would be the correct way to do that.

[TeddyKiller]

Ok, for some reason the foreach if statment doesn't remove empty fields.

Should it ..

if (empty($fields)) {

and not "$field" ?

[premiso]

No, it is $field, but you may want your ternary if statements setup like this:

 

      $fields[] = (isset($_POST['proname']) && !empty($_POST['proname']))?"`proname` = '{$_POST['proname']}'":'';

[Ken2k7]

Seeing how ugly that ternary is, you probably want a function/class method and have it handle it. Just pass it the variable and let it run that so you don't have to type that ugly expression 100 times.

[TeddyKiller]

Ah. Yeah that works! Cheers!

So.. about mysql_real_escape_string() and get_magic_quotes_gpc()

How would I go about securing it.. I can't quite remember the line in which you removed. It was an if statement, checking if there is magic quotes (I'm assuming) to remove them.. else just use mysql_real_escape_string

I just cant think how its done.

 

I remember something similar to this..

$fields = get_magic_quotes_gpc() ? mysql_real_escape_string(stripslashes($fields)) : stripslashes($fields); 

Though I can't remember.. Help?

(Sorry if I'm annoying you. I haven't really done this before.)

 

Ken2k7, yeah. I've got like.. 10 of them, and it looks like giberish. D:

[TeddyKiller]

Uh. What's happened here?

Fatal error: Cannot unset string offsets in /home/jeanie/public_html/editprofile.php on line 191

 

Only occurs when the else statement is in there.

 

      foreach ($fields as $key => $field) {
            if (empty($field)) {
                   unset($fields[$key]);
            } else { $fields = get_magic_quotes_gpc()?mysql_real_escape_string(stripslashes($fields)):stripslashes($fields); }
      }

[Ken2k7]

Dude, you're replacing the $fields array with a string. I think you meant $field. Also, please don't complicate that loop. It was meant to do one task and that else statement just doesn't belong there.

[TeddyKiller]

:| well what should I do to prevent SQL injection? ...

[Ken2k7]

I suggest you escape it upon entering into the array $fields and not wait until the foreach loop.

[TeddyKiller]

So use..

$fields = get_magic_quotes_gpc()?mysql_real_escape_string(stripslashes($fields)):stripslashes($fields);

above the foreach ?

[Ken2k7]

Does it every occur to you that you should look up functions in php.net? What type parameters does mysql_real_escape_string take in? And look up stripslashes. Remember, $fields is an array.

[TeddyKiller]

I looked up the mysql_real_escape_string (I dont tend to look at the comments, as most of them are too complicated) although I came across this.

$_POST = array_map('trim', $_POST);
if(get_magic_quotes_gpc()):
    $_POST = array_map('stripslashes', $_POST);
endif;
$_POST = array_map('mysql_real_escape_string', $_POST);

I'm also assuming you can access the $_POST fields still by doing $_POST['myfield'] ?

[Ken2k7]

Yes, array_map just walks through the array and apply the function to each element in the array.