Proper way to insert variables into a MySQL insert statement

Question

I seem to always have issues getting variables into my MySQL querys.

Is there a good tutorial or could someone show me the proper way of getting variables into the statement?

The main issue I am having is with the different types of quotes (' or ").

Here is my query:

$sql = "INSERT INTO classes (
                        class_name, 
                        user_id, 
                        professor_name, 
                        class_start, 
                        class_end,
                        school_period) 
        VALUES('" . $className 
                . "','" . $userID 
                . "','" . $classProfessor 
                . "','" . $classStart 
                . "','" . $classEnd 
                . "','" . $classPeriod . ");)" 
                . '"';

try this `$sql = "INSERT INTO classes (class_name, user_id, professor_name, class_start, class_end, school_period) VALUES('".$className."','". $userID ."','" . $classProfessor . "','" . $classStart . "','" . $classEnd ."','" . $classPeriod . "')";` — Kumar V, Jan 05 '14 at 05:32
You will need to escape your data. How to do this depends on the database library you are using. — Pekka, Jan 05 '14 at 05:32
@Pekka웃 I just fixed error in query. As you said, it's all depending on the library he used. thanks — Kumar V, Jan 05 '14 at 05:37
What you should be doing is using PDO or mysqli with placeholders, which (almost) entirely removes any need to "embed" variables in your query — Marc B, Jan 05 '14 at 05:41

score 4 · Answer 1 · answered Jan 05 '14 at 06:47

4

Use prepared statements:

<?php
$stmt = $dbh->prepare(
    "INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

answered Jan 05 '14 at 06:47

Markus Malkusch

7,738
2
38
67

score 1 · Answer 2 · answered Jan 05 '14 at 05:45

1

Please do not try to make a correct SQL string yourself. This is how you get SQL Injections.

How to use placeholders

answered Jan 05 '14 at 05:45

siukurnin

2,862
17
20

dev · Accepted Answer · 2014-01-05T12:03:22.903

-4

Use prepared statements: http://php.net/pdo.prepared-statements as that is the best way to execute sql

edited Jan 05 '14 at 12:03

answered Jan 05 '14 at 05:44

dev

439
2
6

1

Is vital to mention the variables need to be escaped, and is much better to use mysqli or PDO. – Ast Derek Jan 05 '14 at 05:45
@AstDerek you are right, I just simplified query at code level. as **its not mentioned which db extension is used.** – dev Jan 05 '14 at 05:50
From the context you can understand OP's code does not escape any variable, otherwise he wouldn't have problems with quotes, or the overall idea of the insertion – Ast Derek Jan 05 '14 at 05:51
@AstDerek its mentioned in question "The main issue I am having is with the different types of quotes (' or "). " so i posted the possible simplification sql string (not query). regarding your view for PDO & mysqli, its always the best idea to use them rather than the old mysql extension as its also deprecated – dev Jan 05 '14 at 05:55
its mentioned in question **Question: "The main issue I am having is with the different types of quotes (' or "). "** – dev Jan 05 '14 at 06:54

Proper way to insert variables into a MySQL insert statement

3 Answers3