im trying to make sql queries through ajax, this is my first time trying this, and i am having problems. the queries are not inserting into the database.
here is how im doing it:
//creating the query
var query2 = "INSERT INTO `player` (`playerid` ,`name` ,`rank` ,`location` ,`robe` ,`bounty` ,`bountydue`)VALUES ('"+userid+"', '"+username+"', '1', '1', '1', '0', '0')";
// calling the ajax fuction
var temp = ajaxdb(query2);
// the ajax function
function ajaxdb(query){
var result = 'koala';
$.ajax({
type: "GET",
url: "ajaxdb.php",
data: "query="+query,
async: false,
success: function(data){
result = data;
}
});
return result;
}
//ajaxdb.php
<?php
include("db.php");
$query = $_REQUEST['query'];
$return = db_execute($query);
echo $return;
?>
//db_execute
function db_execute($dml_command)
{
$conn = db_connect();
$var = mysql_query($dml_command);
$var2 = mysql_fetch_array($var);
mysql_close($conn);
return $var2;
}
any issues with this? thanks!
[web] ajax database queries problems
Well... I have lots of issues with the design you've put together, mainly with the glaring SQL injection opportunity. You may want to Google some tutorials on working with PHP/MySQL for better ways to structure everything.
That aside, you're likely submitting a URL encoded query. Try sending back $_GET['query'] to see what you're actually querying. You can use urldecode() to fix that, but I would really suggest getting your query outside the JS.
If that wasn't it, print out all the variables (make sure they're accurate) and check what errors MySQL spits out and put that data here. Also, you can use [code] tags to keep your source code mostly intact.
That aside, you're likely submitting a URL encoded query. Try sending back $_GET['query'] to see what you're actually querying. You can use urldecode() to fix that, but I would really suggest getting your query outside the JS.
If that wasn't it, print out all the variables (make sure they're accurate) and check what errors MySQL spits out and put that data here. Also, you can use [code] tags to keep your source code mostly intact.
Author
thanks for the reply, ill look into sql injection.
i tried changing the return to what you mentioned and i am getting this back:
INSERT INTO `player` (`playerid` ,`name` ,`rank` ,`location` ,`robe` ,`bounty` ,`bountydue`)VALUES (\'100000664203700\', \'Phumed Stuff\', \'1\', \'1\', \'1\', \'0\', \'0\')
so i can see there are added \ around the values.
any tips on how to better design these queries? i am trying to do all my database queries for the game through ajax and jquery.
i tried changing the return to what you mentioned and i am getting this back:
INSERT INTO `player` (`playerid` ,`name` ,`rank` ,`location` ,`robe` ,`bounty` ,`bountydue`)VALUES (\'100000664203700\', \'Phumed Stuff\', \'1\', \'1\', \'1\', \'0\', \'0\')
so i can see there are added \ around the values.
any tips on how to better design these queries? i am trying to do all my database queries for the game through ajax and jquery.
You used regular apostrophes (') instead of backticks (`) there. Something (probably jQuery, but I haven't used it lately) is escaping them.
Without getting too ridiculous with PHP-DB design, you generally don't want to setup a "execute_query" function that uses a "db_connect" function to do a new connection every query. Just Google some PHP/MySQL tutorials or take a look at some open source software to see how they handle the DB connection.
What you REALLY REALLY REALLY need to do is never put SQL on the client's computer. Some bored hacker will take a look at your JS and replace query2 with "DROP TABLE `player`" just because they can. Keeping the structure you have (generic query AJAX and PHP) takes some tweaking, but here's a rough starting point from your code:
Without getting too ridiculous with PHP-DB design, you generally don't want to setup a "execute_query" function that uses a "db_connect" function to do a new connection every query. Just Google some PHP/MySQL tutorials or take a look at some open source software to see how they handle the DB connection.
What you REALLY REALLY REALLY need to do is never put SQL on the client's computer. Some bored hacker will take a look at your JS and replace query2 with "DROP TABLE `player`" just because they can. Keeping the structure you have (generic query AJAX and PHP) takes some tweaking, but here's a rough starting point from your code:
<?phpinclude("db.php");// using $_GET['action'] to determine query type// nonexistent setup function would be replaced with code belowswitch ( $_GET['action'] ) { case [0]: setup_query0(); break; case [1]: setup_query1(); break; // etc...}// START CASE-DEPENDENT EXAMPLE - query1// this would be a getValuesFromArray()-ish function, not inline and hardcodedforeach ( $_GET as $key => $value ) { switch ( $key ) { case 'userid': $uidToInsert = mysql_real_escape_string($value); break; case 'username': $unameToInsert = mysql_real_escape_string($value); break; }}$query = "INSERT INTO `player` (`playerid` ,`name` ,`rank` ,`location` ,`robe` ,`bounty` ,`bountydue`)VALUES (`$uidToInsert`, `$unameToInsert`, `[1]`, `[1]`, `[1]`, `[0]`, `[0]`)";// END CASE-DEPENDENT EXAMPLE - query1$return = db_execute($query);// Parse $return into meaningful response/JS/JSONecho $return;?><script>// Using fake-array to workaround generic function// rewrite as Object.property for better performance// or use jQueryvar varsToSend = new Array();// Define what you want to dovar varsToSend["action"] = [1]; // Whatever works for you herevar varsToSend["userid"] = userid;var varsToSend["username"] = username';// calling the ajax fuctionvar temp = ajaxdb(varsToSend);// the ajax functionfunction ajaxdb(varArray){var result = 'koala';var uriString = '';for ( key in varArray ) { uriString += key+"="+varArray[key]+"&";}$.ajax({type: "GET",url: "ajaxdb.php",data: uriString,async: false,success: function(data){result = data;}});return result;}</script>
You shouldn't have an SQL query in the javascript that the client can view. Simply append the values you want to INSERT as url-arguments to your PHP file and let the PHP construct the SQL query itself.<br><br>This can help avoid injection problems.
Author
thanks for the help guys! i see what you mean jolid, and will base my code of your example.
Quote:Original post by jolid
What you REALLY REALLY REALLY need to do is never put SQL on the client's computer.
Quoted for emphasis. The others have said it too. SQL injection is real and it's extremely easy to do unless you take precautions, even if you don't write the SQL into javascript on the client :D Never put anything from the user directly into SQL. Always restrict communication to your SQL server to your server-side scripts, always (well, preferably) use some form of prepared statement, and always sanitize the input from the user before setting the statement's parameters.
Author
thanks, its working now and the sql isnt in the javascript.
im having another issue with mysql_num_rows()
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
im using it to determine if the user has an account, or if one needs to be created.
here is the code:
the createAccount works fine, the query is forming properly and echos like this:
Select playerid from player where playerid = xxxxxxxxxxxxxxx
any ideas?
im having another issue with mysql_num_rows()
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
im using it to determine if the user has an account, or if one needs to be created.
here is the code:
//pass in useridvar temp = checkAccount(userid); //pass data ajax function function checkAccount(id){ var args= new Array(); args["action"]= "checkAccount"; args["userid"]= id; var temp = ajaxdb(args); return temp; } //make ajax request function ajaxdb(varArray){ var result; var uriString = ''; for ( key in varArray ) { uriString += key+"="+varArray[key]+"&"; } $.ajax({ type: "GET", url: "ajaxdb.php", data: uriString, async: false, success: function(data){ result = data; } }); return result; } //php ajax file<?phpinclude("db.php");switch ( $_GET['action'] ) { case 'checkAccount': checkAccount(); break; case 'createAccount': CreateAccount(); break;}$return;$query;function checkAccount(){ $uidToInsert = $_GET['userid']; global $query; $query = "Select playerid from player where playerid = ".$uidToInsert; $return = db_execute_count(query);}function createAccount(){ $uidToInsert = $_GET['userid']; $unameToInsert = $_GET['username']; global $query; global $return; $query = "INSERT INTO `player` (`playerid` ,`name` ,`rank` ,`location` ,`robe` ,`bounty` ,`bountydue`)VALUES ('".$uidToInsert."', '".$unameToInsert."', '1', '1', '1', '0', '0');"; $return = db_execute($query);}// Parse $return into meaningful response/JS/JSONecho $query;?>//db execute countfunction db_execute_count($sql){ $conn = db_connect(); $rows = mysql_query($sql); $var = mysql_num_rows(rows); mysql_close($conn); return $var;}the createAccount works fine, the query is forming properly and echos like this:
Select playerid from player where playerid = xxxxxxxxxxxxxxx
any ideas?
The problem seems to be that you forgot the $ sign before "rows".
On another note, do not pass results from $_GET directly into an SQL string. Like previously mentioned, use mysql_real_escape_string() on them first.
On another note, do not pass results from $_GET directly into an SQL string. Like previously mentioned, use mysql_real_escape_string() on them first.
Also, is playerid defined as a string or integer? You'll need to put $uidToInsert in quotes if it's a string. If it's a number, you can use is_int()/is_numeric()/is_whatever-is-appropriate() to validate the input.
This just occurred to me, but why is the userid coming from the JS? Normally UIDs are the auto-incremented table index. If you're working on some kind of a coded login (eg: limited betas or paid subscriptions), that should be separate from the ID to the user record (renewals!). Try checking the username instead.
This just occurred to me, but why is the userid coming from the JS? Normally UIDs are the auto-incremented table index. If you're working on some kind of a coded login (eg: limited betas or paid subscriptions), that should be separate from the ID to the user record (renewals!). Try checking the username instead.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
