PHP- Queried results to new database

Question

The code bellow is used to find a client by entering that client's specific username and password. But i want to save the result from any query to specific table called orders. The problem is that i have trouble with coding that. Here is my code and in the loop which the comment "//save the queried results to new database" is, i want to insert the code for the variables which then will be inserted to the table.

<?php
    mysql_connect("localhost", "hidden", "hidden") or die("Error connecting to database: ".mysql_error());
    mysql_select_db("users") or die(mysql_error());

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Search results</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <link rel="stylesheet" type="text/css" href="style.css"/>
</head>
<body>
<?php
    $query = $_GET['query']; 
    $password = $_GET['password'];

    $min_length = 3;

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

        $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

        $query = mysql_real_escape_string($query);
        // makes sure nobody uses SQL injection

        $raw_results = mysql_query("SELECT * FROM users
            WHERE (`username` LIKE '%".$query."%') AND (`password` LIKE '%".$password."%')") or die(mysql_error());



        if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following

            while($results = mysql_fetch_array($raw_results)){

                //save the queried results to new database
            }

        }
        else{
            echo "Nema rezultati za takov korisnik";
        }

    }
    else{ // if query length is less than minimum
        echo "Minimalnata dolzina na stringot e: ".$min_length;
    }
?>
</body>
</html>

http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1 — , Feb 10 '16 at 21:27
`$query` seems very insecure (based on the name, maybe that is just a bad variable name though). Why not escape `$password` as well? Have you tried writing your `insert` yet? `htmlspecialchars` is the inverse of what you'd want. You want to decode from browser to data source (probably unless you encoded and database...but decoded for browser?). — chris85, Feb 10 '16 at 21:29
@devpro , no i want to insert the name, the city and the address of the queried username — Aleksandar ivanovski, Feb 10 '16 at 21:33

score 2 · Answer 1 · edited May 23 '17 at 12:15

You code has a lot of security issues.

First, you should not pass parameters like username and password as GET parameters. The browser might save the url in history, a user might share the link to twitter. Not safe, pass parameters with POST.

Second, applying mysql_real_escape_string() to username will not protect you from an SQL injection, (see this SQL injection that gets around mysql_real_escape_string()). Use prepared statements please. You should not use htmlspecialchars(), you are not echoing the username to the page. Moreover, you should search the database with the html special characters but not with their html values.

eg, if username = "<username>", search the database with "<username>" and not with "&lt ;username&gt ;". You should only apply htmlspecialchars when you echo the username to the page, eg echo 'Hello '.htmlspecialchars($username).', how are you?';

Third, you use mysql_*, you shouldn't. mysql_* is deprecated in PHP 5.5.0 and removed in PHP 7. Use mysqli_* instead.

Fourth, you should never, query the database with password LIKE %$passwordFromuser%. Imagine you have the password of "hellomypassword" and the username of "Bob". So I login with the credentials: username = "Bob" password = "a", so the expression "%a%" matches "hellomypassword". So i have accessed your account. Same with username, applying '%' to the username is wrong.

Last but not least, use a strong hashing algorithm for storing the passwords to the database.

Now, the first query should return only one single row (only one row should have a specific set of username and password) so you do not need the while($results = mysql_fetch_array($raw_results)){}. Just fetch the results once.

Then use the results and perform an INSERT query to the table like the following INSERT INTO tbName (rowName1,rowName2) VALUES (rowValue1, rowValue2).

In addition: try password verification using PHP's `password_verify()` and `password_hash()` functions [see: http://php.net/manual/en/faq.passwords.php]. These use safer encryption methods. So you would get the user's row from the DB, and then check the submitted password against the password column with `password_verify()`. On creation of the user you store the string that `password_hash()` produces with the chosen password. — Philip, Feb 10 '16 at 22:02

PHP- Queried results to new database

1 Answers1