Insert a PHP variable in a MySql SQL Statement

Question

I'm trying to do like this using PHP and MySql PDO:

//PHP Variables
$msg_a = 'Too Little';
$msg_b = 'Score OK';

$sql = "select if(stdScore >= stdRequired, $msg_a, $msg_b) from scores;"

$results = $conn->prepare($Sql);

$results->execute();

AFAIK this should have worked. But I keep getting the following error message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

How can something like this be done?

Your code is still open to SQL injection. PDO is not a magic bullet, and you need to use it correctly with bound parameters instead of string concatenation — Bojangles
– Bojangles, Commented Jan 3, 2014 at 7:22
Why bother to use prepare if you still interpolate query strings? — peterm
– peterm, Commented Jan 3, 2014 at 7:23
echo $sql; and run it in server to check if query is correct. — Awlad Liton
– Awlad Liton, Commented Jan 3, 2014 at 7:32

R R · Accepted Answer · 2014-01-03 08:32:12Z

3

$results = $conn->prepare($Sql);

---------------------------------------------^ (capital S)

it should be with a lowercase s

$results = $conn->prepare($sql);

because you have:

$sql = "select if(stdScore >= stdRequired, $msg_a, $msg_b) 
        from scores";(//semicolon after double quotes)

---^
with a lowercase s ($sql)

edited Jan 3, 2014 at 8:32

answered Jan 3, 2014 at 7:18

R R

2,9562 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Krish R · Accepted Answer · 2014-01-03 07:59:36Z

2

Can you try this,

$sql = "select if(stdScore >= stdRequired, $msg_a, $msg_b) from scores";

$results = $conn->prepare($sql);

edited Jan 3, 2014 at 7:59

answered Jan 3, 2014 at 7:23

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

3 Comments

MrVimes Over a year ago

Isn't necesary. Php allows you to insert variables directly into strings.

Krish R Over a year ago

@MrVimes, What is not neccessary. ?

MrVimes Over a year ago

using those quote characters. PHP allows direct insertion like this - "Hello world $myvar Hello hello". The questioner's problem is not the string.

zafus_coder · Accepted Answer · 2014-01-03 07:22:18Z

0

Have you tried it this way ?

$sql = "select if(stdScore >= stdRequired, "'.$msg_a.'", "'.$msg_b.'") from scores;"

answered Jan 3, 2014 at 7:22

zafus_coder

4,6312 gold badges14 silver badges13 bronze badges

1 Comment

R R Over a year ago

we usually try the above way as you mentioned when our php variables are not getting interpreted inside the query but i dont think the OP is getting the mentioned error due to this.

peterm · Accepted Answer · 2014-01-03 07:39:23Z

0

Since you're already using PDO don't do query string interpolation leaving your code vulnerable to sql injections and value escaping problems. Instead use prepared statements properly.

Your code could've looked something like

$msg_a = 'Too Little';
$msg_b = 'Score OK';
// use placeholders in a query string
$sql = "SELECT IF(stdScore >= stdRequired, :msg_a, :msg_b) msg FROM scores";
// prepare the statement
$query = $conn->prepare($sql);
// bind parameters and execute the query
$query->execute(array(':msg_a' => $msg_a, ':msg_b' => $msg_b));
// fetch the resultset
$rows = $query->fetchall(PDO::FETCH_ASSOC);

answered Jan 3, 2014 at 7:39

peterm

93k16 gold badges157 silver badges162 bronze badges

Collectives™ on Stack Overflow

Insert a PHP variable in a MySql SQL Statement

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related