Query works in phpmyadmin but not in PHP

Question

Ok so i have this kind of query

SELECT * FROM table WHERE column LIKE 'Blahblahblah Blah - Blah (Blah-Blah)'

(Yep, column values are 20-30 characters long) And it works in phpmyadmin and returns ~ 100 results. But whn i try it in PHP framework CI (CodeIgniter) It is not returning any values. My code looks like:

$sql = "SELECT * FROM table WHERE column LIKE '$val' ORDER BY column ASC";
$sql = $this->db->query($sql);
return $sql->result();

So how do i get this to work?

Are you using a different user account perhaps? Using root in phpmyadmin perhaps? Turn on errors anyhow, it will probably be muchmore helpful in showing you what the problem is. — Fluffeh, Sep 06 '14 at 12:46
You're using like without any wildcard ('%') is that intentional? Does your Blahblah contain any quotation marks? — Mark Baker, Sep 06 '14 at 12:50
@Fluffeh i have many others queries that work well, but only this one is not working — , Sep 06 '14 at 12:50
You're not checking for any errors. I don't know about CI in particular but normally with DB libraries the query() method will inform you some way or another that it failed (by returneing false or null or throwing an exception for example), and then the framework will provide some other methods for determining the last SQL error. You should be writing your code to check for failure and report the error condition that occurred if the query failed. Al — GordonM, Sep 06 '14 at 12:51
Have a look at the string comparison functions MySQL reference page. https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html it might help you find what you need. — Trig3rz, Aug 18 '17 at 13:15

score 0 · Accepted Answer · edited May 23 '17 at 11:53

0

Before you try to make it work, you really, really need to change the way you're constructing the query. You should use a prepared statement where you introduce variables, and then bind them to values.

The way you've written it is horribly vulnerable to SQL injection attacks. (Suppose $val contained '; DROP DATABASE blah; .... What would the whole SQL statement now look like?) If you try to solve the problem in its current form, you'll end up with something that works but will be very dangerous. Make it safe first with a prepared statement.

Details in this linked question.

edited May 23 '17 at 11:53

Community

1
1

answered Sep 06 '14 at 12:50

chiastic-security

20,430
4
39
67

4

This isn't an answer to the question, it's a comment. – GordonM Sep 06 '14 at 12:52
I disagree. It's an answer to the correct question, which is much more valuable than answering the question as it stands. And in any case, the question asks: "How can I get this to work?". I've told the OP the right way to get it to work. Anything else is sticking plaster on a gaping wound. – chiastic-security Sep 06 '14 at 12:52
i know that injection could be done, and i know how to prevent it. I am trying to make this code work, and only then i will prevent injections – Sep 06 '14 at 12:55
1

@DandoZ900 I think any work you put in to make the code work will be wasted, though. If you do it with a prepared statement, it will work anyway. – chiastic-security Sep 06 '14 at 12:57
The problem is that he's got no/inadequate error reporting in his code. If he fixes that he can get a meaningful error message back to help him determine why his query isn't working in PHP. Your answer is about a completely unrelated problem (the SQL injection issue is a real one that needs addressing but it's not the thing causing the problem he's asking the question about) – GordonM Sep 06 '14 at 13:30
Yes, error reporting needs adding too. That and SQL injection both seem to me to be important issues that are part of a complete answer to the underlying question of how to get this to work properly. The question asks for a dab of whitewash; what you're suggesting and what I'm suggesting are both key parts of a full answer to how to get it to work properly. – chiastic-security Sep 06 '14 at 13:51

Query works in phpmyadmin but not in PHP

1 Answers1