3

I need an extra pair of eyes! I have a super-simple query:

$result = $mysqli->query("SELECT post_id FROM blog_posts WHERE post_uri = 'the-test-post' LIMIT 1");
$row = $result->fetch_array();

and this gives me the post_id. However, if I insert a variable for post_uri, the result is empty. Ways I tried of which none worked:

$result = $mysqli->query("SELECT post_id FROM blog_posts WHERE post_uri = '".$post_uri."' LIMIT 1");


$result = $mysqli->query("SELECT post_id FROM blog_posts WHERE post_uri = ".$post_uri." LIMIT 1");


$result = $mysqli->query("SELECT post_id FROM blog_posts WHERE post_uri = $post_uri LIMIT 1");

I have similar query on another page working just right, so that confuses me even more. Help appreciated.

Improve this question
1

3 Answers 3

Reset to default
4

You are slapping a variable directly into a query. This is error prone (as you are discovering) and has a high risk that you'll fail to sufficiently sanitise it (and thus cause an SQL injection vulnerability).

Use the PDO layer and bound variables.

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

1 Comment

Turned out the error was caused by my syntax omission in INSERT query preceding the one I asked about here, and this omission wouldn't even have a chance of happening when approach suggested here is used. Plus it's more secure. Thanks again, really!
2

If you put that query in a string and echo it, you can check what happens. There might be something wrong with that variable!

echo "SELECT post_id FROM blog_posts WHERE post_uri = '".$post_uri."' LIMIT 1";

And so on. I'll bet there's either nothing, or something you're not expecting in that $post_uri, because it shouldn't matter to mysql how you've build your query.

Improve this answer

1 Comment

Thanks for this neat trick, it showed me that query is perfectly fine and I'm obviously messing things somewhere else.
-1

I had a similar problem. Your syntax looks fine. Try to use a simple version of the db connection call. Below are compared the version that worked (above) to the one that failed (below). 

$sqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
$mysqli->real_connect('localhost', 'my_user', 'my_password', 'my_db')

I had use a variable in my query and had a $mysqli->real_connect db connection. That would not work. But when I switched to the new mysqli type I was surprised that the variable query did work.

I hope that works out for you.

Improve this answer

2 Comments

$con->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0') is likely your issue. The code example on Php's documentation shows that and many people copy it without realizing the implications.
'tis getting a little dim by now, but what you say is ringing bells. I am in fact quite certain my code relating to real_connect did come straight from the man[UAL]. And yes, it included the MYSQLI_INIT_COMMAND. The db stuff is rather touchy and trying :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.