N-Bomb(Nerd) Posted May 17, 2009 Share Posted May 17, 2009 Hello, I'm quite excited as I've finally created my first full working script that actually has some meaning to it! There's just one final thing left before I can put a halt to the php scripting and start designing the website. I'm getting some user input on my website, and I'm trying to figure out the safest way to store their input into my database. I've never created a public website before so I'm not sure what's really possible as far as the sql injections go. To be honest though I'm quite horrified about getting my sql injected .. There is only one part in my script where their input is actually outputted back to them and it's wrapped in the htmlentities(). However, I believe they're able to inject still even if I don't output anything to them.. what would be the best thing to do with my input before submitting it to my database? I'm looking for a sure method of defeating all sql injections.. Thanks, N Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/ Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 Best thing to do is to use prepared statements Second best is to escape any user input variables that are put into query strings. Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835758 Share on other sites More sharing options...
N-Bomb(Nerd) Posted May 17, 2009 Author Share Posted May 17, 2009 I'm not quite sure what prepared statements are, however I've heard of escaping. What all would I have to do to properly escape my string, would this be 100% protection from sql injection? Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835852 Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835856 Share on other sites More sharing options...
N-Bomb(Nerd) Posted May 17, 2009 Author Share Posted May 17, 2009 http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html Heh, that actually ended up confusing me even more somehow. :'( Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835914 Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 Bottom line is: mysql_real_escape_string is not 100% sure way to protect your queries from SQL injection. Prepared statements are, because of the way they work. - first you create and send to database a query 'template' with '?' instead of actual variables. It is precompiled by MySQL, so that it structure can not be changed. - then you send variables to be put into a query in place od '?' placeholders. If used properly this give you total protection from SQL injection. Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835931 Share on other sites More sharing options...
N-Bomb(Nerd) Posted May 17, 2009 Author Share Posted May 17, 2009 Bottom line is: mysql_real_escape_string is not 100% sure way to protect your queries from SQL injection. Prepared statements are, because of the way they work. - first you create and send to database a query 'template' with '?' instead of actual variables. It is precompiled by MySQL, so that it structure can not be changed. - then you send variables to be put into a query in place od '?' placeholders. If used properly this give you total protection from SQL injection. That seems to make a bit more sense, thanks! However, I've never really worked with this and I just starting learning the mysql functions now I need mysqli? I tried looking at a few examples and came up with this, doesn't seem to be working and it's outputting "broke". $conn = mysqli_connect($Host, $Username, $Password, $Database); if (!mysqli_connect_errno()) { if ($stmt = mysqli_prepare($conn, "SELECT `First_Name`, `Last_Name` FROM INFO WHERE Id=?")) { mysqli_stmt_bind_param($stmt, "s", $this-> Id); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $First, $Last); mysqli_stmt_fetch($stmt); mysqli_stmt_close($stmt); echo 'First Name: ' . $First . '<br>Last Name: ' . $Last; } else { echo 'broke'; } mysqli_close($conn); } Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835970 Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 Yeah, prepared statements are only supported by mysqli. Worry not. It works mostly like mysql, except it's better I think this mysqli_stmt_bind_param($stmt, "s", $this-> Id); should be mysqli_stmt_bind_param($stmt, "s", $this->Id); Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835974 Share on other sites More sharing options...
N-Bomb(Nerd) Posted May 17, 2009 Author Share Posted May 17, 2009 Yeah, prepared statements are only supported by mysqli. Worry not. It works mostly like mysql, except it's better I think this mysqli_stmt_bind_param($stmt, "s", $this-> Id); should be mysqli_stmt_bind_param($stmt, "s", $this->Id); I just tried this and ran the function and it keeps outputting "broke", so the else on the if statement is firing.. so I believe something is wrong with this line: if ($stmt = mysqli_prepare($conn, "SELECT `First_Name`, `Last_Name` FROM INFO WHERE Id=?")) { Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835984 Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 Try echoing mysqli_error instead of 'broke' Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-835995 Share on other sites More sharing options...
N-Bomb(Nerd) Posted May 17, 2009 Author Share Posted May 17, 2009 Try echoing mysqli_error instead of 'broke' Psh, are you kidding? Everyone loves having errors in their script and having it output as "broke". Anyways, I echoed mysqli_error and received this output: Unknown command broke FYI, I kept the "broke" echo just because it's now officially considered the cool way of trying to debug your script. Edit: I changed mysqli_error to mysqli_errno and got the output: "1047" Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-836000 Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 http://bugs.php.net/bug.php?id=30656 Which MySQL Server version do you have? Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-836004 Share on other sites More sharing options...
N-Bomb(Nerd) Posted May 17, 2009 Author Share Posted May 17, 2009 http://bugs.php.net/bug.php?id=30656 Which MySQL Server version do you have? So, I'm guessing I should make my database MySQL 5.0 instead of MySQL 4.0? I have the option to do so in my control panel, would simply changing it do anything to my database or will I need to reconstruct it? Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-836008 Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 Ahh... yess... This bug report was filled in 2004 and MySQL 4.0 was outdated THEN! As for updating you should ask your hosting company probably. I've never used any automated migration tools for MySQL. Just make sure you do a backup prior to anu updates Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-836011 Share on other sites More sharing options...
N-Bomb(Nerd) Posted May 17, 2009 Author Share Posted May 17, 2009 All seems to be working well with the creation of the new database. I have one more question, and I believe I'll be good to go.. After this: if ($stmt = mysqli_prepare($conn, "SELECT `First_Name`, `Last_Name` FROM INFO WHERE Id=?")) { is ran, is there a way to figure out if the query was ran successfully and the Id existed and the vaules for `First_Name` and `Last_Name` was pulled? In my previous script with only mySQL I had something like: if ($Query) { if (mysql_num_rows($Query) > 0) { // Value exists.. } else { // Value doesn't exist.. } } Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-836029 Share on other sites More sharing options...
Mchl Posted May 17, 2009 Share Posted May 17, 2009 http://www.php.net/manual/en/mysqli-stmt.num-rows.php Take a close look at the examples. You need to store result before calling this. Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-836030 Share on other sites More sharing options...
fenway Posted May 19, 2009 Share Posted May 19, 2009 Prepared statements aren't great for performance, though... they're per-connection. Also, if you properly escape special characters -- with mysql_real_escape_string() or your own personal function -- it doesn't get any *safer*. Quote Link to comment https://forums.phpfreaks.com/topic/158472-making-user-input-safe/#findComment-837340 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.