PHP prepared sql statement breaks when calling column with special chars

Question

I'm calling a php script which breaks every time I call a column (which has an url in it example http://www.sample.com/something).

The error which I get is PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4294967296 bytes)

I don't want to solve the problem by extending the memory which I doubt would solve the problem anyways.

The problem appears after I try to execute the prepared statement (after this line of code my script breaks $stmt->execute();

The problems is definitely because of the slash character '/' which is in my url's column. If I call any other column without a '/' sign, the statement executes correctly and the data is rendered.

Here is more of my code:

$stmt = $conn->prepare("
SELECT post.id, content_cite, url FROM post, data 
WHERE post.id = data.post_FK AND data.age < ? AND data.age <> ?");


$stmt->bind_param("ii", $one, $two);

// params
$one = 35;
$two = 0;
$stmt->execute();

// select header params
$stmt->bind_result($col_1, $col_2, $col_3);
while ($stmt->fetch()) {
    $json = array(
        'id' => $col_1,
        'content_cite' => $col_2,
        'url'=> $col_3

    );

    array_push($contentArray, $json);
}

As I've said the url column is the problem. If this statement is ran inside an SQL editor it executes without issues.

Any ideas what's happening here?

Oh I see where @CMiller is going here. If you use `bindResult()` on a `LONGTEXT` PHP doesn't know how large of a result might get dumped in there, so it tries to allocate a memory segment of the max size to accomodate it. For a `LONGTEXT` that's 4GB. Skip `bindResult()` and just use `$row = fetch()` in your loop. — Sammitch, Dec 26 '17 at 18:49
OMG, I would have never thought of that!! I'll try to fix that... tnx guys for the computer science... — EugenSunic, Dec 26 '17 at 18:50
@CMiller you should post an answer so you can take credit for figuring it out. :P — Sammitch, Dec 26 '17 at 19:03
I only suspected that the data type might be related, but I did not know the actual solution as I mostly deal with PDO which I don't think has a bindResult(). — C Miller, Dec 26 '17 at 19:06

score 0 · Answer 1 · answered Dec 26 '17 at 18:41

0

I can't see the / character having anything to do with this, but I'll wager that the url column is larger than the others you've tried, so retrieving many of those could push you over the memory limit.

Try changing:

FROM post, data 
WHERE post.id = data.post_FK

To:

FROM post INNER JOIN data 
  ON post.id = data.post_FK

For a far more efficient JOIN operation.

answered Dec 26 '17 at 18:41

Sammitch

30,782
7
50
77

ok, I'l try that, but not sure why this would cause a problem, since when I execute with the php script with no prepared statement it works... – EugenSunic Dec 26 '17 at 18:42
@Sammitch. I always assumed that the only difference between the two was simply syntax. Did not know the performance varied. Now I am interested in it. Is there anywhere I can go to read about this? – C Miller Dec 26 '17 at 18:46
@CMiller nowhere off the top of my head, but with the original form of the `JOIN` you're entirely dependent on how smart the query optimizer is to catch the conditions and internally change it into an `INNER JOIN`. If it's not smart, [which is frequently the case with mysql] it will do a `FULL JOIN` [aka join every column in `post` with every column in `data`] and *then* take that N^2 result set and filter it in the `WHERE` clause. @CMiller caught the actual cause of the problem, but being more explicit with your `JOIN` syntax is something you should get into the habit of regardless. – Sammitch Dec 26 '17 at 18:53
Very cool. I may have to go revisit some queries were I was a bit lazy in the past and didn't know about that. I now use the INNER JOIN command, but it was more so for readability and have a lot of older code where I did not. – C Miller Dec 26 '17 at 18:57
The implicit join as OP currently uses is not any less/more efficient than explicit join as you recommend. All RDMS should have same execution plan. See [Inner join vs Where](https://stackoverflow.com/questions/121631/inner-join-vs-where). But OP should still use `JOIN` as it is current standard and aids in readability. – Parfait Dec 26 '17 at 21:14

EugenSunic · Accepted Answer · 2017-12-26T20:06:39.177

0

No need for releasing/abandoning bind_param
no need for INNER JOIN instead of WHERE

This solves the problem:

 $stmt = $conn->prepare("
    SELECT post.id, content_cite, url FROM post, data 
    WHERE post.id = data.post_FK AND data.age < ? AND data.age <> ?");


    $stmt->bind_param("ii", $one, $two);

    // params
    $one = 35;
    $two = 0;
    $stmt->execute();


// if you have longtext as a type in your database you must call this method
        mysqli_stmt_store_result($stmt);


$stmt->bind_result($col_1, $col_2, $col_3);
    while ($stmt->fetch()) {
         $json = array(
            'id' => $col_1,
            'content_cite' => $col_2,
            'url'=> $col_3

        );

        array_push($contentArray, $json);
    }

Tnx Prepared mysqli select statement on longtext field is coming back empty

edited Dec 26 '17 at 20:06

answered Dec 26 '17 at 19:59

EugenSunic

13,162
13
64
86

1

*no need for INNER JOIN instead of WHERE* ... you are currently using the implicit join and not explicit join the current ANSI SQL standard introduced 25 years ago. It does not affect performance but does in readability and maintainability. See [Inner join vs Where](https://stackoverflow.com/questions/121631/inner-join-vs-where). – Parfait Dec 26 '17 at 21:13
I'm aware of that sir, I know that where and inner join did not make any difference in my problem (nor would it make a difference in any other problem) just wanted to point that out in my answer. – EugenSunic Dec 27 '17 at 17:01

PHP prepared sql statement breaks when calling column with special chars

2 Answers2