Notretsam Posted September 2, 2015 Author Share Posted September 2, 2015 (edited) delete and select queries on my site don't come from forms with text fields that can be inputed, some combos where items can be selected. That why am thinking am good on that front. EDIT" actually the where comparison in query will be a variable, dam , thought i could go with the smaller easier code. oh well don't call execute twice? u mean below part $stmt->execute();if (!$stmt->execute()) { Edited September 2, 2015 by Notretsam Link to comment Share on other sites More sharing options...
justsomeguy Posted September 2, 2015 Share Posted September 2, 2015 delete and select queries on my site don't come from forms with text fields that can be inputed, some combos where items can be selected. That why am thinking am good on that front.That doesn't mean anything, any arbitrary data can be submitted to any page. I don't need to use your form to create a post request to your PHP code. It's those kinds of assumptions that get people in trouble. The question is if you are using variables in your query, not how they're getting there. If you're picking and choosing when to use security then you have to get it right 100% of the time, an attacker only needs to get it right once. Just make a habit of always doing it.don't call execute twice? u mean below partYes, you're executing the query twice. Every time you call execute you run the query. Link to comment Share on other sites More sharing options...
Notretsam Posted September 2, 2015 Author Share Posted September 2, 2015 (edited) I understand what u mean now justsomeguy Am just about to put together a UPDATE query thank you very much for the help, do very much appreciate it. Edited September 2, 2015 by Notretsam Link to comment Share on other sites More sharing options...
Notretsam Posted September 2, 2015 Author Share Posted September 2, 2015 got update, insert, delete, select queries prepared, binded and working now. am sorted now, thank you very much justsomeguy, you was a big help and much appreciate it Link to comment Share on other sites More sharing options...
justsomeguy Posted September 2, 2015 Share Posted September 2, 2015 No problem, glad you got it worked out. Link to comment Share on other sites More sharing options...
Notretsam Posted September 3, 2015 Author Share Posted September 3, 2015 well sadly I didn't get select query working right , thought I had but messed up with test. according to http://www.piliapp.com/php-syntax-check/ nothing wrong with code but $visitID is not setting for some reason. include("theme/mem.inc");// Create connection$conn = new mysqli($host, $user, $password, $database);// Check connectionif ($conn->connect_error) { die("Connection failed: " . $conn->connect_error);}$stmt = $conn->prepare("SELECT * FROM memberInfo WHERE wrestlerName= ?");$stmt->bind_param('s',$var1);// set parameters and execute$var1 = "Scottish Phantom";if (!$stmt->execute()) { echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;}$stmt->close();$conn->close();$visitID = "$memid"; Link to comment Share on other sites More sharing options...
justsomeguy Posted September 3, 2015 Share Posted September 3, 2015 Make sure you have error messages being displayed: ini_set('display_errors', 1);error_reporting(E_ALL);It doesn't look like you're setting $memid anywhere. Link to comment Share on other sites More sharing options...
Notretsam Posted September 3, 2015 Author Share Posted September 3, 2015 $memid is a mysql column that meant to be selected from memberInfo database Link to comment Share on other sites More sharing options...
justsomeguy Posted September 3, 2015 Share Posted September 3, 2015 Then you need to set that variable to the value you get from the database. Link to comment Share on other sites More sharing options...
Notretsam Posted September 3, 2015 Author Share Posted September 3, 2015 $visitID = "$memid"; that is what that meant to do Link to comment Share on other sites More sharing options...
Notretsam Posted September 3, 2015 Author Share Posted September 3, 2015 (edited) obviously the information contained in database isn't being extracted with code I have so $memid is blank and why it not set. just don't know how to add the old extract($row) command I used before to get info. i see fetch_assoc while loops in some examples i looked at on stack overflow, but really want to extract all info and not have to set individual variables like these examples show. Edited September 3, 2015 by Notretsam Link to comment Share on other sites More sharing options...
Ingolme Posted September 3, 2015 Share Posted September 3, 2015 In what part of your code are you actually giving $memid a value? I don't see it anywhere. Also, don't wrap variables in quotation marks unless you intend to put some other text between them. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 3, 2015 Share Posted September 3, 2015 You can use bind_result to bind variables to your database columns, and then when you use fetch it will put the values from the next row into those variables.http://php.net/manual/en/mysqli-stmt.bind-result.phphttp://php.net/manual/en/mysqli-stmt.fetch.phpYou shouldn't use select * though, you should list the specific columns you want so that you can also list the variables to put each value into.If you want to use extract then you need to use store_result to get the results as a mysqli_result object, and then you can use fetch_assoc on the result object to get the array you can extract. Link to comment Share on other sites More sharing options...
Notretsam Posted September 4, 2015 Author Share Posted September 4, 2015 I always find php.net examples hard to follow is there really any chance of someone using sql injection with select queries? I do use a variable to select info I want, but someone would need to know what that variable is to inject code I would think. Link to comment Share on other sites More sharing options...
Notretsam Posted September 4, 2015 Author Share Posted September 4, 2015 (edited) well get below working but coding seems rather long, compared to the simpler and smaller code I usually use. just need to figure out how to do while loop so i can get more than one result // Create connection$conn = new mysqli($host, $user, $password, $database);$fsloginName = 'Confirmed';// Check connectionif ($conn->connect_error) { die("Connection failed: " . $conn->connect_error);}/* create a prepared statement */if ($stmt = $conn->prepare("SELECT memid, wrestlerName FROM memberInfo WHERE accStatus=?")) { /* bind parameters for markers */ $stmt->bind_param("s", $fsloginName); /* execute query */ $stmt->execute(); /* bind result variables */ $stmt->bind_result($memid,$wrestlerName); /* fetch value */ $stmt->fetch(); $visitID = "$memid"; $wrestlerName = "$wrestlerName"; /* close statement */ $stmt->close();}echo "ID = $visitID - wrestlerName = $wrestlerName"; Edited September 4, 2015 by Notretsam Link to comment Share on other sites More sharing options...
thescientist Posted September 4, 2015 Share Posted September 4, 2015 is there really any chance of someone using sql injection with select queries? absolutely. ANYTIME time you are accepting any data from an outside request and use it in your code, you should validate and sanitize that data (this isn't limited to just when used with SQL queries, btw). you should really read up about SQL injection if you're still unclear. In fact, w3schools uses the SELECT query as an example. http://www.w3schools.com/sql/sql_injection.asp Also, relevant XKCD https://xkcd.com/327/ Link to comment Share on other sites More sharing options...
Notretsam Posted September 4, 2015 Author Share Posted September 4, 2015 don't fully understand sql injection but do understand enough to know that people add text to there field inputs that they submitting , that will comprise you're database. however I don't see how they can inject if there is no text field for them to type in. I did think maybe in the URL they could do page.php?varname=therecodehere But they would need to know what the varname is I would think. I did search for sql injection attack examples and found below link, which gave me a bit more info on it. http://www.unixwiz.net/techtips/sql-injection.html Am not disputing that SQL Injection is a thing, it obviously is, just don't see how an attacker can do it, if there no text field for them to use. Link to comment Share on other sites More sharing options...
Notretsam Posted September 4, 2015 Author Share Posted September 4, 2015 I got select single query and while loop figured out for multiple results the $numberofrows is setting 0 though, so not got that right, not sure why /* create a prepared statement */if ($stmt = $conn->prepare("SELECT memid, wrestlerName FROM memberInfo WHERE accStatus=? ORDER BY wrestlerName")) { /* bind parameters for markers */ $stmt->bind_param("s", $fsloginName); /* execute query */ $stmt->execute(); $numberofrows = $stmt->num_rows; /* bind result variables */ $stmt->bind_result($memid,$wrestlerName); echo "$numberofrows<br>"; /* fetch value */ while ($row = $stmt->fetch()) { echo "ID = $memid - wrestlerName = $wrestlerName<br>";} /* close statement */ $stmt->close();} Link to comment Share on other sites More sharing options...
justsomeguy Posted September 4, 2015 Share Posted September 4, 2015 is there really any chance of someone using sql injection with select queries?Sure, one common SQL injection attack is on a login form where you can log in as any user without knowing their password.just need to figure out how to do while loop so i can get more than one resultYou can use while ($stmt->fetch()). Every time you call fetch it will populate the bound variables with the values from the next record. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 4, 2015 Share Posted September 4, 2015 Am not disputing that SQL Injection is a thing, it obviously is, just don't see how an attacker can do it, if there no text field for them to use.If you're using values from the database rather than a form, for example maybe you're using the username that you got from the user table, at some point the user typed that in. That is user-supplied data, it doesn't matter if you're using it on a login form or registration form, or if at some point later in your application you're getting it from the database and using it in another query. If you're not always treating it properly then there's going to be a vulnerable query somewhere. That's why there's no reason to try and guess when to be safe and when not to be safe, it's better to just always be safe.In other words, once data is dirty, it's always dirty. Just because you got a value from a database doesn't mean it's safe to use unescaped in any other query. If the user typed that data in at any point then it's dirty.the $numberofrows is setting 0 though, so not got that right, not sure whyIf you're using num_rows with a prepared statement, you need to use store_result first. The result set is buffered, so it doesn't know how many results there are until you store the result.http://php.net/manual/en/mysqli-stmt.num-rows.phphttp://php.net/manual/en/mysqli-stmt.store-result.php Link to comment Share on other sites More sharing options...
thescientist Posted September 4, 2015 Share Posted September 4, 2015 don't fully understand sql injection but do understand enough to know that people add text to there field inputs that they submitting , that will comprise you're database. however I don't see how they can inject if there is no text field for them to type in. I did think maybe in the URL they could do page.php?varname=therecodehere But they would need to know what the varname is I would think. I did search for sql injection attack examples and found below link, which gave me a bit more info on it. http://www.unixwiz.net/techtips/sql-injection.html Am not disputing that SQL Injection is a thing, it obviously is, just don't see how an attacker can do it, if there no text field for them to use. That's likely because you're only assuming that a browser is the only way to make an HTTP request. There are many other ways to make a request to your server, such as a command line utility like cURL. If a user makes requests through your site to the server they can see that in the network tabs. From there they can just reproduce that request through command line for example, in a script that runs in a loop until it get's a hit. Link to comment Share on other sites More sharing options...
Notretsam Posted September 4, 2015 Author Share Posted September 4, 2015 (edited) If you're using num_rows with a prepared statement, you need to use store_result first. The result set is buffered, so it doesn't know how many results there are until you store the result.http://php.net/manual/en/mysqli-stmt.num-rows.phphttp://php.net/manual/en/mysqli-stmt.store-result.php yup store result is what I was missing. thanks, working now. think am set now on upgrading to mysqli and prepared statements, going make a start on upgrading site to it today. *fingers crossed* it goes well and don't have anymore questions lol we shall see. don't know if there is a way for moderators to change titles of a thread, but this thread certainly turned into a completely different topic from original title. have no probs with topic of thread being changed to "sql injection - mysqli prepared statements" or something like that. lots of good info in here i feel for those like me that needed to upgrade. Edited September 4, 2015 by Notretsam Link to comment Share on other sites More sharing options...
Notretsam Posted September 4, 2015 Author Share Posted September 4, 2015 I figured there was something I was missing on sql injection, didn't consider that the login fields are being carried over to other pages, an therefore any sql injection attacks can be carried over with it. thanks for the info. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now