Why This INSERT Doesn't Work...?


#21

I completely agree with @spaceshiptrooper. For far too many people security is an afterthought which most of the time doesn’t ever get implemented. We should not coddle beginners. The need to know right up front if there code is dangerous.

If the OP stopped what he was doing to learn prepared statements FIRST he more than likely would have ended up not having the “insert problem” by the time he was done.


#22

Wow. I think in some ways a lot of you have forgotten what it’s like to be a newbie.

Let’s face it - prepared statements are NOT the most straight forward concepts to wrap your head around. Yes, prepared statements are more secure and yes, that’s the proper way to do the work. But for someone just starting out, they can be daunting to get right.

Oh, and has anyone noticed that with 20 replies in this thread, the OP hasn’t returned? Gee,I wonder why…only a handful of replies here (two in the beginning, though @donboe tried to get it back on track later) actually tried to help figure out the actual issue, or point the OP in the right direction. Everything else went down the self-righteous “don’t be stupid! Why are you doing it that old way” attitude/path that so many “help” forums take. Don’t forget you were learning once too.


#23

@DaveMaxwell, do you really think helping a newbie learn how to do it wrong is the right approach? And if he did bail because he couldn’t handle truthful expert responses then he probably shouldn’t be coding anyways.

My forum experience shows that if they don’t return they probably just went to one of the other ten cross posts where someone just handed them the answer and used that “because it works”.

I wish I had encountered such responses when I was a noob. It would have saved me a whole lot of wasted time.


#24

I believe everyone is right here. It may be too complex for a beginner to understand, but we shouldn’t forget that other members are also going to use the codes as well seeing g that everything now a days is copy and paste.

But I think maybe we should leave the thread to die since the OP isn’t coming back.


#25

Nope. That’s not what I’m saying at all…

Agree to disagree here. There are two ways to handle issues on a help forum like this:

  1. Tell them they’re wrong, to go away and read up on topics and then come back
  2. Actually help them with their immediate problem BUT point out that there is a better way and why, and even provide an example for how to do it the better way to allow them to relate it to their initial issue. And provide them relevant links to read up further when they have time.

Guess which way is going to encourage people to learn? Guess which approach will encourage people to learn the right way (using other pages from the sites the links provided)? Guess which way causes forums to grow? Hmmm…seems like #2

Guess which approach this thread took? Yeah, not #2. So I don’t blame to OP if they just took a “working” example from somewhere else and now has no guidance to do it the right way…

Edit: THIS is a helpful, expert type of reply. Thank you @ahundiak!


#26

@robertbralic007 you posted that the INSERT does not work.

Without knowing what error messages, if any. you were getting, we know two things.

  • there might be a problem with the PHP code
  • there might be a problem with the query

I can not speak for others, but I can say what I do when I am faced with this.

* note that while working up code during development I always have as much error reporting as possible.

To be fair, yes, writing your code in a syntax that helps you to avoid and spot errors is a very good idea. And yes, security should be integral not an afterthought.

Currently I have gotten into the habit of testing queries outside of PHP before I write any PHP code for them. I prefer the CLI (Command Line Interface) but I think phpMyAdmin is good to use too if you’re more comfortable with that.

Only after I am as certain as I can be that the query works as I want it to do I then start writing the PHP.

Some prefer mysqli_, some PDO. Some prefer procedural syntax, others OOP syntax. Sadly many seem to still be writing obsolete mysql_ that is a FAIL waiting to happen sooner rather than later. It is also sadly true that many write insecure code and are satisfied with “it works”.

Anyway, here’s what I suggest.

  • if you don’t have error reporting and error display set up, do so. It will let you know of syntax errors and other problems as well as make trouble shooting easier during development.
  • if you get an error pointing to a database problem instead of a PHP problem, test the query outside of any PHP code.

#27

@robertbralic007,

It is often useful to start with something simple and then expand once it is working. Here is a functioning example using only two columns for clarity:

<?php
// Always start with this so you can see your errors
error_reporting(E_ALL);

$link = new mysqli('localhost', 'user', 'password', 'mysqli');

// Some sample values, no real need to deal with POST for now
$b_mjestro  = 'mjestro';
$b_restoran = 'restoran';

// This works but is very insecure
$query = 
    "INSERT INTO restoran (b_mjesto,b_restoran) 
     VALUES ('$b_mjestro','$b_restoran');";
$link->query($query);

// Prepared statements should be used instead
$stmt = $link->prepare(
    'INSERT INTO restoran (b_mjesto,b_restoran) VALUES (?,?);'
);
$stmt->bind_param('ss',$b_mjestro,$b_restoran);
$stmt->execute();

You can actually put the above code in a php file and then test it from a console window with ‘php filename.php’; You might find it easier to understand what is going on without having to make an html page and deal with posting and whatnot.

The first example is what everyone is talking about when they say things like “sql injection”. Just avoid it.

The second example is more secure. There are actually a number of possible variations. You can for example use names instead of ? marks as placeholders. Here is a more detailed example of using prepared statements.

Enjoy


#29

[off topic]
Reminds me of the footnote from a very knowledgeable member that was something like…

make it work then make it better

Perhaps could have added “and safer”
[/off topic]


#30

you can type ’ var_dump($query) ',then copy the query sql to sql reomte,such as adminphp.if the sql can’t run,it will return your error message.