php mysql search continues forever

Question

I'm trying to create a basic "search my table columns for all rows that match, and show me the rows" type search. What I ended up with is a query that repeats forever, and the resulting webpage never stops loading. Classic example of a loop that can't end. I don't know why though. I'm trying to keep each little step in it's own function like a good boy, and reusing functions.

A person should be able to search for question or john to return one row, plus or minus looks like two rows, and test will return three rows.

Please help!

function databaseconnection($sql){
    $usernm="XXXfooXXX";
    $passwd="XXXfooXXX";
    $host="XXXfooXXX";
    $database="contact_info";
    mysql_connect($host,$usernm,$passwd);
    mysql_select_db($database);
    $result = mysql_query ($sql) or die (mysql_error ());
    return $result;
}

function searchtable(){
    echo 'searchtable() <br />';
    if ($_POST['search'] != "" ){
        $search = preg_replace('/[^ \wa-zA-Z0-9_.@()\-+~,?]+/', '', $_POST['search']);
    }
    if ($search){
        $sql = "SELECT * FROM names_numbers WHERE name LIKE \"%{$search}%\"".
            " OR phone_address LIKE \"%{$search}%\"".
            " OR notes LIKE \"%{$search}%\"";
        echo 'attempting sql action<br />';
        echo "$sql <br />";
        while ($row = mysql_fetch_assoc(databaseconnection($sql))){
            print_r($row);
        }
    }
}

function draw_search_form(){
    echo '<form action="./numbers.php" method="post">'."\n";
    echo 'notes:<br>'."\n";
    echo '<input type="text" name="search"><br>'."\n";
    echo '<input type="submit" value="Submit">'."\n";    
    echo '</form>'."\n";   
}

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! ***SQL Injection!*** *It's not just for breakfast any more!* — Jay Blanchard, Nov 07 '16 at 18:45
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Nov 07 '16 at 18:45
regarding injection attacks, I'm also stripping all but a few basic characters _.@()-+~,? before the POST data is ever moved over to a $var for easier use. Is it still realistically possible to inject without the use of = & ' " | `$search = preg_replace('/[^ \wa-zA-Z0-9_.@()\-+~,?]+/', '', $_POST['search']);` — bradboy, Nov 07 '16 at 20:03
ok, I'm going to be bringing myself up to speed with PDO, will I still need to worry about injection when i make the switch? If so, can you point me to a resource or to so I can improve my understanding of the attack? — bradboy, Nov 07 '16 at 20:07
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Jay Blanchard, Nov 07 '16 at 20:08

Jerry · Accepted Answer · 2016-11-07T19:25:25.220

1

I believe in your while loop, you are executing the query every time, which resets your result iterator. When you switch to mysqli or PDO you can refactor to run the query once, then iterate over the results.

$results = databaseconnection($sql);
while($row = mysqli_fetch_assoc($results) {
    ...
}

edited Nov 07 '16 at 19:25

answered Nov 07 '16 at 18:49

Jerry

3,391
1
19
28

That's not to say you can't refactor this in `mysql_*`, but as noted above, yo shouldn't. – Jerry Nov 07 '16 at 18:51
`//$results = mysql_fetch_assoc(databaseconnection($sql)); //foreach ($results as $row){ // print_variable($row); //}` doesn't quite achieve the desired result either – bradboy Nov 07 '16 at 19:21
I haven't used that interface in a while, but I added what I think id the correct method. But note the problem pretty much goes away by itself if you switch to non-deprecated methods. – Jerry Nov 07 '16 at 19:27
your corrections worked perfectly! Now, since I've been out of the coding game for a while, time to go investigate PDO. It seems I'm in the dark ages. (sets my telephone receiver on my modem) – bradboy Nov 07 '16 at 19:37

score 0 · Answer 2 · answered Nov 07 '16 at 19:33

With your current while loop, you are opening a new database connection, running the query and getting the first row again and again.

This can be easily fixed by moving your query out of database, and then iterating over the result

Here is the code with changes

function databaseconnection($sql){
    $usernm="XXXfooXXX";
    $passwd="XXXfooXXX";
    $host="XXXfooXXX";
    $database="contact_info";
    mysql_connect($host,$usernm,$passwd);
    mysql_select_db($database);
    $result = mysql_query ($sql) or die (mysql_error ());
    return $result;
}

function searchtable(){
    echo 'searchtable() <br />';
    if ($_POST['search'] != "" ){
        $search = preg_replace('/[^ \wa-zA-Z0-9_.@()\-+~,?]+/', '', $_POST['search']);
    }
    if ($search){
        $sql = "SELECT * FROM names_numbers WHERE name LIKE \"%{$search}%\"".
            " OR phone_address LIKE \"%{$search}%\"".
            " OR notes LIKE \"%{$search}%\"";
        echo 'attempting sql action<br />';
        echo "$sql <br />";
        // Run the query once
        $result = databaseconnection($sql);

        // Now iterate over the results
        while ($row = mysql_fetch_assoc($result)){
            print_r($row);
        }
    }
}

function draw_search_form(){
    echo '<form action="./numbers.php" method="post">'."\n";
    echo 'notes:<br>'."\n";
    echo '<input type="text" name="search"><br>'."\n";
    echo '<input type="submit" value="Submit">'."\n";    
    echo '</form>'."\n";   
}

score 0 · Answer 3 · answered Nov 07 '16 at 19:36

posting as an answer, to make it easier to format the corrected function:

function searchtable(){
    echo 'searchtable() <br />';
    if ($_POST['search'] != "" ){
        $search = preg_replace('/[^ \wa-zA-Z0-9_.@()\-+~,?]+/', '', $_POST['search']);
    }
    if ($search){
        $sql = "SELECT * FROM names_numbers WHERE name LIKE \"%{$search}%\"".
            " OR phone_address LIKE \"%{$search}%\"".
            " OR notes LIKE \"%{$search}%\"";
        echo 'attempting sql action<br />';
        echo "$sql <br />";
        $results = databaseconnection($sql);
        while($row = mysql_fetch_assoc($results)) {
            print_variable($row);
        }
        //while ($row = mysql_fetch_assoc(databaseconnection($sql))){
        //    print_variable($row);
        //}
    }
}

score 0 · Answer 4 · answered Nov 07 '16 at 19:43

0

Man, you are providing an array into while which will always remain true. Please use foreach instead.

$row = mysql_fetch_assoc(databaseconnection($sql)) foreach ($row as $r){ print_r ($r)//extract associative elements }

answered Nov 07 '16 at 19:43

Abhay Maurya

11,819
8
46
64

php mysql search continues forever

4 Answers4