CODEWITHSUNDEEP
regexperlreplacedouble-quotes
user977802
user977802

Reputation: 1

Perl replaces single quotes to double quotes string

My Script want to replace or remove the single quotes with double quotes str_replace(rtrim(c_manager),~s/\'/\'\'/g) this line cannot work it out..

Example: k'amal

Result : k"amal or kamal

$sql = 'select rtrim(f_admin_disabled),'."\n".

                '       convert(varchar,t_password,101),'."\n".

                '       rtrim(c_email),'."\n".

                '       str_replace(rtrim(c_manager),~s/\'/\'\'/g),'."\n".

                '       rtrim(c_mgr_email)'."\n".

                '  from tuserprofile'."\n".

                ' where ic_user1 = '."'$user_id'"."\n";


         $sth = $dbh->prepare("$sql")
            or err("Database error in $sql", "Error preparing SQL statement:\r\n\n" . $dbh->errstr, 3);
         $sth->execute or err("Database error in $sql", "Error executing SQL statement:\r\n\n" . $dbh->errstr, 3);
         $sth->bind_columns(\$prev_status, \$prev_date, \$prev_email, \$prev_mngr_name, \$prev_mngr_email);
         $sth->fetch();
         $sth->finish();

         if($user_email ne $prev_email){

            $sql = 'declare @result int'."\n".
                   'exec @result = ap_recert_update '."'$user_id', '$prev_date', ".
                                                      "'$prev_status', '$user_email', ".
                                                      "'$prev_mngr_name', '$prev_mngr_email'  "."\n".
                   'SELECT @result'."\n";

            $sth = $dbh->prepare("$sql")
               or err("Database error in $sql", "Error preparing SQL statement:\r\n\n" . $dbh->errstr, 3);
            $sth->execute or err("Database error in $sql", "Error executing SQL statement:\r\n\n" . $dbh->errstr, 3);
            $sth->bind_columns( \$result);
            $sth->fetch();

            if($result < 0){
               err("", $user_id."\t".$result, 0);
               $problem = $problem.$user_id."\t".$result."\n";
            }
            $sth->finish();
         }
      }
   }

Upvotes: 0

Views: 301

Answers (1)

Miller
Miller

Reputation: 35198

Don't include your variables directly in your sql statements. Instead use placeholders and bind variables.

Cleaning up your first sql statement would be done like follows:

my $sql = q{select rtrim(f_admin_disabled),
    convert(varchar,t_password,101),
    rtrim(c_email),
    str_replace(rtrim(c_manager),~s/'/''/g),
    rtrim(c_mgr_email)
    from tuserprofile
    where ic_user1 =?};

$sth = $dbh->prepare($sql)
    or err("Database error in $sql", "Error preparing SQL statement:\r\n\n" . $dbh->errstr, 3);
$sth->execute($user_id) or err("Database error in $sql", "Error executing SQL statement:\r\n\n" . $dbh->errstr, 3);
$sth->bind_columns(\($prev_status, $prev_date, $prev_email, $prev_mngr_name, $prev_mngr_email));
$sth->fetch();
$sth->finish();

Also could just use a heredoc for the assignment to $sql

my $sql = <<'END_SQL';
select rtrim(f_admin_disabled),
  convert(varchar,t_password,101),
  rtrim(c_email),
  str_replace(rtrim(c_manager),~s/'/''/g),
  rtrim(c_mgr_email)
  from tuserprofile
  where ic_user1 =?
END_SQL

Upvotes: 4

Related Questions