How to return a MySQL select function as a string?

Question

I've been working on my first big website and encountered a problem while trying to create a PHP function that returns a value based on criteria given when the function is called.

I have been having a problem for using the following code, all I could get to return was "DATABASE: 1"

function data_r_user($request, $username ) {
    include("login/dbconnect.php");
    $sqli = "SELECT " . $request . " FROM users WHERE username = " . $username;
    $result = mysqli_query($dbconnect,$sqli ) or die();

    return $result;
}

After searching up for a while I appear to of tracked the problem to the fact that all SQL queries return an array and not a string (I believe this to be the case sorry if I'm wrong).

I thought returning a single result would be easy but it appears not to be.

So basically I want a way to return a single result from this function and I'm indifferent as to whether it's procedural or object.

$return is not defined and your query is invalid and open to SQL injections. Strings need to be quoted. Once you get the query working use fetch $result and set it to $return. Also or die() is kind of useless, output something there so you know why it died, or use the error reporting function so you get information. — chris85
– chris85, Commented Dec 4, 2016 at 19:27
Read the manual about $result type of mysqli_query() - php.net/manual/ru/mysqli.query.php — Roman Che
– Roman Che, Commented Dec 4, 2016 at 19:31
yeah the return was a fault on my end because i removed some items so it was focusing on where the issue was. fixed it now. — 117PeterM
– 117PeterM, Commented Dec 4, 2016 at 19:32
$sqli is still an invalid query so you will result in the die. See the rest of the comment. — chris85
– chris85, Commented Dec 4, 2016 at 19:33
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST or $_GET data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Dec 5, 2016 at 3:14

WEBjuju · Accepted Answer · 2016-12-04 19:40:24Z

3

Using prepared statements is highly recommended, so forgive me for not pointing you to mysqli_fetch_row but instead showing you how to do this securely:

function data_r_user($request, $username ) {
    // you want require and only do it once
    require_once("login/dbconnect.php");

    $return = '';

    // white list the allowed columns
    $columns_allowed = array('id', 'username', 'firstname', 'lastname');
    if (! in_array($request, $columns_allowed)) {
        // if they ask for something not allowed give them nothing
        return '';

    }

    /* create a prepared statement */
    if ($stmt = mysqli_prepare($dbconnect, "SELECT " . $request . " FROM users WHERE username = ?")) {

      /* bind parameters for markers */
      mysqli_stmt_bind_param($stmt, "s", $username);

      /* bind result variables */
      mysqli_stmt_bind_result($stmt, $return);

      /* fetch value */
      mysqli_stmt_fetch($stmt);     
    }

    return $return;
}

edited Dec 4, 2016 at 19:40

answered Dec 4, 2016 at 19:33

WEBjuju

6,6814 gold badges31 silver badges37 bronze badges

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

2 Comments

WEBjuju Over a year ago

ah, i focused on $username and blanked on the column. i'll update it.

tadman Over a year ago

A white-list like this is a much safer way of doing it. Good answer.

Collectives™ on Stack Overflow

How to return a MySQL select function as a string?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related