SQL question

  • Hi,
    I’ve a little problem with my wordpress site…

    I have a list of persons, when i click on one person, i want to show some details.
    There are two different tables in my database.
    In the first table the name is called r_name. In the second table it is called u_name.
    But the names are the same!!
    Now i want to run this code:

    $aktPerson = $_GET['r_name'];
  global $wpdb;
  $dbname = $wpdb->prefix . 'u_persons';
  $rows = $wpdb->get_results("SELECT * from $dbname WHERE u_name = $aktPerson");

    I get this error:

    [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Christian' at line 1]
SELECT * from wp_lehrerVisitenkarten WHERE l_name = SMITH Christian

    I hope somebody can help me…

Viewing 1 replies (of 1 total)

  • The problem is that in the SQL generated by your function the name is not quoted:

    SELECT * from wp_lehrerVisitenkarten WHERE l_name = SMITH Christian

    That SQL needs to look like this:

    SELECT * from wp_lehrerVisitenkarten WHERE l_name = 'SMITH Christian'

    The simplest change would be to put single quotes around the $aktPerson variable in your get_results() call, but there’s still a problem: Your current code is extremely vulnerable to an SQL injection attack. You’re taking user input ($_GET['r_name'], which is coming from the URL) and putting it directly into an SQL query. This is a big no-no.

    Instead you should use the $wpdb->prepare() function to safely insert user-supplied values into SQL by ‘preparing’ the query. You can read the documentation for $wpdb->prepare() here, but in your example the correct usage would be:

    global $wpdb;

$aktPerson = $_GET['r_name'];
$dbname    = $wpdb->prefix . 'u_persons';

$query = $wpdb->prepare( "SELECT * from $dbname WHERE u_name = %s", $aktPerson );
$rows  = $wpdb->get_results( $query );

    Done this way, $wpdb will add the correct quotes to $aktPerson for you, and escape it so that it can’t be used to manipulate the query.

    • This reply was modified 5 years, 9 months ago by Jacob Peattie.
Viewing 1 replies (of 1 total)
  • The topic ‘SQL question’ is closed to new replies.