PHP MySQL query containing keywords/reserved words

Question

I have encountered an issue with updated my MySQL data which includes HTML data, I continuously fixed errors; however, once one error is fixed it gives another. The current error is as follows:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc='Live updates to certain games will also be posted on this website througho' at line 1

I have been scavenging on Stack Overflow for nearly 3 days without any definitive answers. So I am hoping someone can find this!

Here is my PHP form code:

 if (isset($_POST['submit'])) { 
    $WName = mysql_prep($_POST['wname']);
    $SName = mysql_prep($_POST['sname']);
    $Desc = mysql_prep($_POST['desc']);
    $LogoURL = mysql_prep($_POST['logourl']);
    $aboutPage = mysql_prep($_POST['aboutpage']);
    $query = "UPDATE settings SET name='$WName',subName='$SName',desc='$Desc',logoUrl='$LogoURL',about='$aboutPage'";
    // $query = mysql_prep($query);
    mysql_query($query) or die(mysql_error());
     header("Location: settings.php?=success");
 } 

The function

mysql_prep()

can be found on the internet, namely here: https://gist.github.com/ZachMoreno/1504031

Here is the HTML form:

<form role="form" action="" method="post">
    <!-- text input -->
    <div class="form-group">
        <label>Website Name</label>
        <input type="text" name="wname" class="form-control" placeholder="
            <?php echo $row['name']; ?>" value="
            <?php echo $row['name']; ?>" />
        </div>
        <div class="form-group">
            <label>Sub Name</label>
            <input type="text" name="sname" class="form-control" placeholder="
                <?php echo $row['subName']; ?>" value="
                <?php echo $row['subName']; ?>" />
            </div>
            <div class="form-group">
                <label>Description</label>
                <textarea name="desc" class="form-control" rows="3" placeholder="
                    <?php echo $row['desc']; ?>" >
                    <?php echo $row['desc']; ?>
                </textarea>
            </div>
            <div class="form-group">
                <label>Logo URL</label>
                <input type="text" name="logourl" class="form-control" placeholder="
                    <?php echo $row['logoUrl']; ?>" value="
                    <?php echo $row['logoUrl']; ?>"  />
                </div>
                <div class="form-group">
                    <label>About Page</label>
                    <textarea class="form-control" name="aboutpage" rows="6" placeholder="
                        <?php echo $row['about']; ?>">
                        <?php echo $row['about']; ?>
                    </textarea>
                </div>
                <div class="box-footer">
                    <input type="submit" name="submit" class="btn btn-primary" value="Submit" style="margin-left:-10px;" />
                </div>
            </form>

Thanks very much for any assistance that you can provide, I hope this can be figured out and I aim to use this to assist future visitors who encounter the same/similar issues.

Answer 1

Can't believe I didn't see this earlier; the issue I had with MySQL was that the database had the column name 'desc' which I originally had the idea that it meant 'description' but in fact it was conflicting with the keyword 'descending'. This gave the syntax error.

Here is what I found on the MySQL documentation; 9.3 Keywords and Reserved Words :

Keywords are words that have significance in SQL. Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions.

On that web link above you can see a list of keywords/reserved words that shouldn't be used or should include back slashes (which I won't go into).

My solution? Don't use reserved words as identifiers!

The easiest solution that you can do is to simply avoid using these words. I prevented using the reserved word 'desc' by changing the identifier to 'description'.

Thanks for all your help! Hope this assists people in the future.

Answer 2

The string returned from your mysql_prep() function has escaped single quotes. So.. ..you can't use these as delimiters in your query string. Change them to double quotes.

$query = "UPDATE settings SET name    = \"$WName\", 
                              subName = \"$SName\", 
                              desc    = \"$Desc\", 
                              logoUrl = \"$LogoURL\", 
                              about   = \"$aboutPage\" ";

Can you try a $testQuery with just text..

$testQuery = "UPDATE settings SET name    = \"ABC\", 
                                  subName = \"DEF\", 
                                  desc    = \"GHI\", 
                                  logoUrl = \"JKL\", 
                                  about   = \"MNO\" ";

Also, you are missing a WHERE clause, or is there only 1 row?