I want to set null in a table column if the input submitted by a user is an empty string.
More specifically the user may enter or not the wwwaddress of his site-if he has one.
Here is the code that checks if the user submitted an empty string or not and setting NULL to the
db table if indeed he has done so.
function ajax_update_address($address,$city,$municipality,$wwwaddress)
{
global $conn;
$conn->set_charset("utf8");
if($wwwaddress==' ')
{$www=NULL;}
$result = $conn->query('update busines_users
set address="'.$address.'",city="'.$city.'",municipality="'.$municipality.'",wwwaddress="'.$www.'"
where crID="12"');
if (!$result){
echo 'problem dude.';
return false;
}
else
{return true;}
}
Unfortunately it does not work,in the db table in the wwwaddress column I still get an empty string.
This conditional you see fails to do what it supposes to do.:
You’ve got a space between the quotes, so your check wouldn’t actually match an empty string. You’re better off using [fphp]empty[/fphp] to check the value as this will catch both spaces, empty strings and other ‘empty’ values:
It’s because you are trying to insert ‘NULL’ (with the quotes) and not null. As it stands right now your are wide open to injection attacks and such. You really should look at prepared queries. Or use one of the many libraries available.
Just to clarify, your current code is generating:
UPDATE business_users SET www = ‘’ WHERE
But basically you need to end up with: (note the lack of quotes around NULL)
UPDATE business_users SET www = NULL WHERE …
And of course if www does have a value then you need (note the quotes around the address)
UPDATE business_users SET www = ‘sitepointebuilder.com’ WHERE …
Might want to fire up the mysql console command and do a few updates manually until the difference is clear.
Ι am posting the whole code…it is a function that includes the update statement:
function ajax_update_address($address,$city,$municipality,$wwwaddress)
{
global $conn;
$conn->set_charset("utf8");
if (empty($wwwaddress)) {
$wwwaddress= NULL;
}
$result = $conn->query('update busines_users
set address="'.$address.'",city="'.$city.'",municipality="'.$municipality.'",wwwaddress="'.$wwwaddress.'"
where crID="12"');
if (!$result){
echo 'houston...we have a problem.';
return false;
}
else
{return true;}
}
As ahundiak pointed out, you should be using a prepared statement (or escaping, at the very least) for your DB query. Here’s how you could rewrite your function (assuming that you’re using the mysqli DB extension):
function ajax_update_address($address, $city, $municipality, $wwwaddress)
{
global $conn;
$conn->set_charset("utf8");
if (empty($wwwaddress)) {
$wwwaddress = NULL;
}
$stmt = $con->prepare("UPDATE busines_users SET address=?, city=?, municipality=?, wwwaddress=?");
$stmt->bind_param("ssss", $address, $city, $municipality, $wwwaddress);
if (!$stmt->execute()) {
// Uncomment line below to troubleshoot query errors
//echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
return FALSE;
} else {
return TRUE;
}
}
Note that it’s good practice to avoid using global variables in your code too. In this situation you could pass the $con object into the function as an argument.
What is the reason for using a prepared statement?
And regarding the thing about the global variables you are mentioning…you are right, will pass it as an argument instead.
Using prepared statements ensures that your data is properly escaped, which prevents SQL injection attacks (where an attacker tries to pass specially crafted SQL into your script, in an attempt to compromise your DB).
No, prepared statements also escape your data, so either use them or escape your data separately, but not both. You mention using mysql_real_escape_string so am I right in thinking you’re using the mysql extension? If so, you should update your code to use the mysqli extension (or PDO), as the mysql functions are depreciated and are going to be removed from PHP.