Surround string with quotes

Question

Is there a function in PHP that adds quotes to a string?

like "'".str."'"

This is for a sql query with varchars. I searched a little, without result...

I do the following:

$id = "NULL";
$company_name = $_POST['company_name'];         
$country = $_POST['country'];
$chat_language = $_POST['chat_language'];
$contact_firstname = $_POST['contact_firstname'];
$contact_lastname = $_POST['contact_lastname'];
$email = $_POST['email'];
$tel_fix = $_POST['tel_fix'];
$tel_mob = $_POST['tel_mob'];       
$address = $_POST['address'];       
$rating = $_POST['rating'];

$company_name = "'".mysql_real_escape_string(stripslashes($company_name))."'";
$country = "'".mysql_real_escape_string(stripslashes($country))."'";
$chat_language = "'".mysql_real_escape_string(stripslashes($chat_language))."'";
$contact_firstname = "'".mysql_real_escape_string(stripslashes($contact_firstname))."'";
$contact_lastname = "'".mysql_real_escape_string(stripslashes($contact_lastname))."'";
$email = "'".mysql_real_escape_string(stripslashes($email))."'";
$tel_fix = "'".mysql_real_escape_string(stripslashes($tel_fix))."'";
$tel_mob = "'".mysql_real_escape_string(stripslashes($tel_mob))."'";
$address = "'".mysql_real_escape_string(stripslashes($address))."'";
$rating = mysql_real_escape_string(stripslashes($rating));

$array = array($id, $company_name, $country, $chat_language, $contact_firstname, 
$contact_lastname, $email, $tel_fix, $tel_mob, $address, $rating);
$values = implode(", ", $array);

$query = "insert into COMPANIES values(".$values.");";

Answer 1

Firstly, I see you're using stripslashes(). That implies you have magic quotes on. I would suggest turning that off.

What you might want to do is put some of this in a function:

function post($name, $string = true) {
  $ret = mysql_real_escape_string(stripslashes($_POST[$name]));
  return $string ? "'' . $ret . "'" : $ret;
}

and then:

$company_name = post('company_name');

All this does however is reduce the amount of boilerplate you have slightly.

Some have suggested using PDO or mysqli for this just so you can use prepared statements. While they can be useful it's certainly not necessary. You're escaping the fields so claims of vulnerability to SQL injection (at least in the case of this code) are misguided.

Lastly, I wouldn't construct a query this way. For one thing it's relying on columns in the companies table being of a particular type and order. It's far better to be explicit about this. I usually do this:

$name = mysql_real_escape_string($_POST['name']);
// etc
$sql = <<<END
INSERT INTO companies
(name, country, chat_language)
VALUES
($name, $country, $language)
END;

That will sufficient for the task. You can of course investigate using either mysqli or PDO but it's not necessary.

Answer 2

Create your own.

function addQuotes($str){
    return "'$str'";
}

Answer 3

Don't do this. Instead use parametrized queries, such as those with PDO.

Answer 4

Rather than inserting the value directly into the query, use prepared statements and parameters, which aren't vulnerable to SQL injection.

$query = $db->prepare('SELECT name,location FROM events WHERE date >= ?');
$query->execute(array($startDate));

$insertContact = $db->prepare('INSERT INTO companies (company_name, country, ...) VALUES (?, ?, ...)');
$insertContact->execute(array('SMERSH', 'USSR', ...));

Creating a PDO object (which also connects to the DB and is thus a counterpart to mysql_connect) is simple:

$db = new PDO('mysql:host=localhost;dbname=db', 'user', 'passwd');

You shouldn't scatter this in every script where you want a DB connection. For one thing, it's more of a security risk. For another, your code will be more susceptible to typos. The solution addresses both issues: create a function or method that sets up the DB connection. For example:

function localDBconnect($dbName='...') {
    static $db = array();
    if (is_null($db[$dbName])) {
        $db[$dbName] = new PDO("mysql:host=localhost;dbname=$dbName", 'user', 'passwd');
        $db[$dbName]->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    return $db[$dbName];
}

If you're working with an array of more than two or three elements, you should use loops or array functions rather than a long sequence of similar statements, as is done in the sample code. For example, most of your sample can be replaced with:

$array = array();
foreach ($_POST as $key => $val) {
    $array[$key] = "'" . mysql_real_escape_string(stripslashes($val)) . "'";
}

Here's a more comprehensive example of creating an insert query. It's far from production ready, but it illustrates the basics.

$db = localDBconnect();

// map input fields to table fields
$fields = array(
  'company' => 'company_name',
  'country' => 'country',
  'lang' => 'chat_language',
  'fname' => 'contact_firstname',
  'lname' => 'contact_lastname',
  'email' => 'email',
  'land' => 'tel_fix',
  'mobile' => 'tel_mob',
  'addr' => 'address',
  'rating' => 'rating',
);
if ($missing = array_diff_key($fields, $_POST)) {
    // Form is missing some fields, or request doesn't come from the form.
    ...
} else {
    $registration = array_intersect_key($_POST, $fields);

    $stmt = 'INSERT INTO `dbname`.`Companies` (`' 
        . implode('`, `', $fields) . '`) VALUES (' 
        . implode(', ', array_fill(0, count($registration), '?')) . ')';
    try {
        $query = $db->prepare($stmt);
        $query->execute(array_values($registration));
    } catch (PDOException $exc) {
        // log an 
        error_log($exc);
        echo "An error occurred. It's been logged, and we'll look into it.";
    }
}

To make it production ready, the code should be refactored into functions or classes that hide everything database related from the rest of the code; this is called a "data access layer". The use of $fields shows one way of writing code that will work for arbitrary table structures. Look up "Model-View-Controller" architectures for more information. Also, validation should be performed.