quotation marks in PHP and mySQL

Question

I'm having trouble putting together mySQL queries containing quotation marks when I have to put them through PHP statements that also use quotation marks, and this gets even messier when I add PHP variables. So far, the best I've come up with is something like this:

$sqlQuery = 'SELECT document FROM `mentioned_places` WHERE name="'.$mentionedPlace.'";';

Which is really just a quagmire of quotation marks. Is there a simpler way to do this?

Use prepared statements (mysqli/pdo) and you will be relieved from this mess :) — HamZa, Jul 23 '13 at 10:39
http://php.net/manual/en/mysqli.prepare.php http://php.net/manual/en/pdo.prepare.php — Ian Kenney, Jul 23 '13 at 10:40
Also, you can just use a variable directly in a double quoted string: `$a = "Hello $world";` — user254875486, Jul 23 '13 at 10:41

score 2 · Answer 1 · answered Jul 23 '13 at 10:40

2

Escape everything. If you are using mysql_ statements, stop using them as they are deprecated. Take a look at PDO or Mysqli.

Both of them escape symbols if you prepare the queries, so you also prevent sql injection.

answered Jul 23 '13 at 10:40

Sergi Juanola

6,531
8
56
93

score 2 · Answer 2 · answered Jul 23 '13 at 10:41

2

To secure your application you should use prepared statements with MySQLi or PDO.

Then you can separate your variables from your query and bind them to the statement.

answered Jul 23 '13 at 10:41

René Höhle

26,716
22
73
82

peterm · Answer 3 · 2013-07-23T10:52:16.747

You can use double quotes:

$sqlQuery = "SELECT document FROM `mentioned_places` WHERE name='$mentionedPlace'";

But you're better off to use prepared statements either with mysqli or PDO.

Using mysqli:

$db = new mysqli(...);
$sql = "SELECT document FROM `mentioned_places` WHERE name = ?";
$query = $db->prepare($sql);
$query->bind_param("s", $mentionedPlace);
$query->execute();
$query->bind_result($document);
$documents = array();
while ($query->fetch()) {
    $documents[] = $document;
}
$db->close();

Using PDO:

try {
    $db = new PDO('mysql:host=localhost;dbname=test;charset=UTF8', 'user', 'userpwd');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    $sql = "SELECT document FROM `mentioned_places` WHERE name = ?";
    $query = $db->prepare($sql);
    $query->execute(array($mentionedPlace));
    $documents = $query->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo "Exeption: " .$e->getMessage(); //TODO better error handling
}
$query = null;
$db = null;

score 0 · Answer 4 · edited May 23 '17 at 12:03

0

You can follow this procedure for give quotation for MYSQL query

Refer this Link and 2.links

Its more useful. Better you can use this link

edited May 23 '17 at 12:03

Community

1
1

answered Jul 23 '13 at 10:47

VIVEK-MDU

2,483
3
36
63

quotation marks in PHP and mySQL

4 Answers4