CODEWITHSUNDEEP
phpmysqlsql
Matt
Matt

Reputation: 147

SQL error retrieving data

I keep running this code and get the same error no matter what I change.

require('common.php');
$charname = $_SESSION['user']['username'];
$query = "SELECT group, guild, username, class, level 
      FROM DD_users 
      WHERE username = '".$charname."'";
try
{
// These two statements run the query against your database table.
$stmt = $db->prepare($query);
$stmt->execute();
}
catch(PDOException $ex)
{
// Note: On a production website, you should not output $ex->getMessage().
// It may provide an attacker with helpful information about your code. 
die("Failed to run query: " . $ex->getMessage());
}

// Finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();

//print_r($rows);
$group = $rows['0']['adminaccess'];
$guild = $rows['0']['guild'];
$username = $rows['0']['username'];
$class = $rows['0']['class'];
$level = $rows['0']['level'];

It returns this error
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 'group, guild, username, class, level FROM DD_users ' at line 1 And apparently I need more text to be able to edit this...

Upvotes: 1

Views: 1166

Answers (4)

DWright
DWright

Reputation: 9500

Something like the following would save you the hassles with the single quotes--but more importantly it would also guard you against a SQL injection attack. You NEVER want to take input and push it straight into a SQL query string. Terrible things can happen.

Note the ? mark in the query string, and the passing of $charname value to substitute for the ? via the execute(array($charname)) call. Doing things this way will let the underlying library code safely quote $charname into the query.

require('common.php');
$charname = $_SESSION['user']['username'];
$query = "SELECT `group`, guild, username, class, level 
          FROM DD_users 
          WHERE username = ?";
try
{
    // These two statements run the query against your database table.
    $stmt = $db->prepare($query);
    $stmt->execute(array($charname));
}
catch(PDOException $ex)
{
    // Note: On a production website, you should not output $ex->getMessage().
    // It may provide an attacker with helpful information about your code. 
    die("Failed to run query: " . $ex->getMessage());
}

// Finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();

print_r($rows);

Upvotes: 1

Ahmed
Ahmed

Reputation: 1789

You forgot ' character:

$query = "SELECT group, guild, username, class, level 
          FROM DD_users 
          WHERE username = '".$charname."'";

Upvotes: 5

periklis
periklis

Reputation: 10188

Watch that the group keyword is reserved, try enclosing it in backticks `

Upvotes: 3

mindandmedia
mindandmedia

Reputation: 6825

try this:

$query = "SELECT group, guild, username, class, level FROM DD_users WHERE username = '".$charname."'";

note the additional quotes '. They are required if you query for a string.

also: group might be a reserved keyword. you need to escape that with `-style quotes or .brackets [. try which works

Upvotes: 4

Related Questions