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. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Nov 26 '13 at 21:56

score 2 · Accepted Answer · answered Nov 26 '13 at 18:55

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 '13 at 18:55

Tesserex

17,166
5
66
106

score 0 · Answer 2 · answered Nov 26 '13 at 18:56

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.

Using $_POST in mySQL query

2 Answers2