Trouble with creating database table using jQuery ajax

Question

I am absolutely new to jQuery and ajax. Currently I am trying to create a table on my local sql server from a javascript file from which I am posting the statement to .php file to execute the statement.

.js file:

function executeStatement(sqlStatement){
    $.ajax({
      type: "post",
      data: sqlStatement,
      cache: false,
      url: "api.php",
      dataType: "text",
      error: function(xhr, status, error) {
        var err = eval("(" + xhr.responseText + ")");
        alert(err.Message);
      },
      success: function ()
      {
        alert ("Success!!");
      }
    });
  }

.php file:

  require_once('PhpConsole.php');
  PhpConsole::start();
  debug('HERE!!!');

  $sqlStatement = $_POST['sqlStatement'];

  $host = "*****";
  $user = "*****";
  $pass = "*****";
  $databaseName = "db_user_data";

  // Create connection
  $con = mysqli_connect($host, $user, $pass, $databaseName);

  // Check connection
  if (mysqli_connect_errno($con)){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  else{
    $con->query($sqlStatement);
    header('Location: success.php');  
  }

I use PHP Console to debug .php files but in this case even the first log 'HERE!!!' is not printed to the console so I am wondering whether it even reaches this .php file. Anyway the event success within executeStatement method is reached and 'Success' printed even though there are no changes in the database.By the way the .php file is also executed on the local server. Does someone has any ideas where the problem can be??

Thanks in advance

why send the whole query from javascript. just send the data you need in json format. {username: 'hello', password: 'world'}, set the dataType option in the ajax function as json, then in PHP, decode the json and do your PHP query in the PHP file — ashley
– ashley, Commented Mar 30, 2013 at 12:48
actually the sqlStatement variable contains something like 'CREATE TABLE bla bla' and it varies depending on the user — Husky
– Husky, Commented Mar 30, 2013 at 13:00
Oh my! I really hope you know what you're doing here. Allowing just ANY SQL statement to be sent to your backend is very insecure. Anyone that is able to access that page is able to delete your database or export sensitive data. — thaJeztah
– thaJeztah, Commented Mar 30, 2013 at 13:30
I know what are you thinking about and dont worry about it :) — Husky
– Husky, Commented Mar 30, 2013 at 13:33
is crazy even putting any sort of server side query statement in javascript... only send applicable data and create statements server side using results of $_POST — charlietfl
– charlietfl, Commented Mar 30, 2013 at 15:11

domo270897 · Accepted Answer · 2013-03-30 13:35:10Z

2

There is a typo in your PHP code at the "$pass" variable:

require_once('PhpConsole.php');
PhpConsole::start();
debug('HERE!!!');

$sqlStatement = $_POST['sqlStatement'];

$host = "*****";
$user = "*****";
**$pass = "*****";**
$databaseName = "db_user_data";

// Create connection
$con = mysqli_connect($host, $user, $pass, $databaseName);

// Check connection
if (mysqli_connect_errno($con)){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else{
  $con->query($sqlStatement);
  header('Location: success.php');  
}

EDIT: Here is my revised JS code - this works perfectly for me as I am able to pass the code from an AJAX call to the PHP code and back. Try this:

var sqlStatement = "sqlStatement=SQLSTATEMENTHERE";
$.ajax({
  type: "POST",
  data: sqlStatement,
  cache: false,
  url: "api.php",
  success: function ()
  {
    alert ("Success!!");
  }
});

Place the variable outside of your function and the ajax call inside to replace the old one. As for the PHP, i'll check that out in a second.

edited Mar 30, 2013 at 13:35

answered Mar 30, 2013 at 12:46

domo270897

587 bronze badges

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

13 Comments

Husky Over a year ago

thats not the problem, I just made it while I was replacing the real info with '*' but thanks for the warning!

domo270897 Over a year ago

oh right, my bad then! - Do you mind posting a typical example of the posted $sqlStatement variable here? That may be where the problem is.

Husky Over a year ago

Sure! For instance like this: 'CREATE TABLE test (TelNr1 INT(64), address CHAR(64));'. The error is not in the sqlStatement, I tried to execute it directly in phpAdmin and I worked well...

domo270897 Over a year ago

Right, that checks out, so the only other place the error could be is in the JS. What i'm thinking now is that the 'sqlstatement' variable is not getting passed to the php code for some reason. Give me a few minutes and i'll have a look for you.

domo270897 Over a year ago

you know how normal links are structured like url.com/index.php?var=1? an ajax call is very similar. It takes the url value and places any value(s) in the data parameter at the end. So without my change, you would be calling: api.php?CREATE TABLE... which dosen't work. With my change, you are now calling api.php?sqlStatement=CREATE TABLE..., which is picked up correctly by PHP.

|

Collectives™ on Stack Overflow

Trouble with creating database table using jQuery ajax

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related