Using $_POST in mySQL query

Question

I am trying to create a program where the user will enter a state abbreviation in a form field and the output will be colleges that are in that state. Here is my code:

<?php
$abbr = $_POST;
print_r ($abbr);
$host='x';
$dbname='x';
$user='x';
$pass='x';
try {
  # MySQL with PDO_MYSQL
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOException $e) {
    echo $e->getMessage();
}

$STH = $DBH->query("
SELECT INSTNM, STABBR
FROM colleges
WHERE STABBR ='$abbr'
");

# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_ASSOC);

while($row = $STH->fetch()) {
    echo $row['INSTNM'] . "\n";
    echo $row['STABBR'] . "<br>";
}
?>

Outputs:

Array ( [STABBR] => AL )

The program works fine when the state abbreviation is hard coded in. So for example, if I do:

$STH = $DBH->query("
SELECT INSTNM, STABBR
FROM colleges
WHERE STABBR ='AL'
");

...colleges that are in Alabama will show up.

I have tried many methods, but none have worked so far. I appreciate the help.

By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks. Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. bobby-tables.com/php has examples to get you started, and this question has many examples in detail. — Andy Lester
– Andy Lester, Commented Nov 26, 2013 at 21:56

Tesserex · Accepted Answer · 2013-11-26 18:55:50Z

2

As your output shows, $_POST is an array, not a single element.

Use

$abbr = mysql_real_escape_string($_POST['STABBR']);

to both get the right element, and to prevent an injection attack.

answered Nov 26, 2013 at 18:55

Tesserex

17.3k6 gold badges72 silver badges108 bronze badges

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

Comments

Marc B · Accepted Answer · 2013-11-26 18:56:48Z

0

This is incorrect:

$abbr = $_POST;
$sql = " .... = '$abbr'";

$_POST is an array, and when you directly use an array in a string context, you get the literal word "Array", so your query is actually

$sql = " .... = 'Array'";

You need to use the name of the form field element:

<input type="text" name="state" value="AL" />
                         ^^^^^---field name
$abbr = $_POST['state'];
                ^^^^^--- field name again

Also note that you are vulnerable to SQL injection attacks.

answered Nov 26, 2013 at 18:56

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

Using $_POST in mySQL query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related